Difference between revisions of "Table Creation and Management"

From rbachwiki
Jump to navigation Jump to search
 
(9 intermediate revisions by the same user not shown)
Line 27: Line 27:


= Modifying Existing Tables =
= Modifying Existing Tables =
==General Syntax ==
==Drop (Delete a Column) ==
  ALTER TABLE tablename
  ALTER TABLE tablename
  ADD|MODIFY|DROP COLUMN| columnname [definition]
  DROP COLUMN columnname;


== Adding a column to an existing Table ==
== Adding a column to an existing Table ==
Line 43: Line 43:
  ALTER TABLE books
  ALTER TABLE books
  MODIFY (title VARCHAR2(10));
  MODIFY (title VARCHAR2(10));
ALTER TABLE publisher
MODIFY (rating DEFAULT 'N');


== Adding Primary Key Constraints ==
== Adding Primary Key Constraints ==
Line 50: Line 53:
  '''system-generated constraint name, which isn’t as helpful'''
  '''system-generated constraint name, which isn’t as helpful'''
  ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);
  ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);
== DROP PRIMARY KEY ==
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;


== Foreign Key Constraint ==
== Foreign Key Constraint ==
Line 66: Line 73:
  ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);
  ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);
=Creating NEW Tables =
=Creating NEW Tables =
 
CREATE TABLE a_animals(
animal_id NUMBER(6),
  name VARCHAR2(25),
license_tag_number NUMBER(10),
admit_date DATE CONSTRAINT ail_admit_date_nn NOT NULL,
adoption_id NUMBER(5),
vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,
  CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
  CONSTRAINT ail_license_pk UNIQUE(license_tag_number)
);
== Default Values ==
== Default Values ==
  CREATE TABLE acctmgr(
  CREATE TABLE acctmgr(
Line 77: Line 93:
== Creating a table based on another table (subquery) ==
== Creating a table based on another table (subquery) ==
  CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);
  CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);
== ADDING COMMENTS TO COLUMNS ==
''' PROBLEMS IS THE TABLE.DESCRIPTION IS THE COLUMN '''
COMMENT ON COLUMN problems.description IS 'Short description of the problem';


[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]
[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Latest revision as of 20:09, 12 November 2017

Identify the table name and structure
Commands used to create or modify database tables are called data definition language(DDL commands
A Database Object is a defined self contained structure also called database tables
Creating Tables Description
CREATE TABLE Creates a new table in the database. The user names the columns and identifies the type of data to be stored. To view a table, use the SQL*PLUS

command DESCRIBE.

Modifying Tables
ALTER TABLE . . . ADD Adds a column to a table.
ALTER TABLE . . . MODIFY Changes a column size, datatype, or default value.
ALTER TABLE . . . DROP COLUMN Deletes one column from a table
ALTER TABLE . . . SET UNUSED or SET UNUSED COLUMN Marks a column for deletion at a latertime.

Modifying Existing Tables

Drop (Delete a Column)

ALTER TABLE tablename
DROP COLUMN columnname;

Adding a column to an existing Table

This adds ext to the end of the publisher table

ALTER TABLE publisher
ADD (ext NUMBER(4));

MODIFY COMMAND Change a column size, datatype or default value

  • Changing Column Size (increase or decrease)
  • Changing the datatype (such as varchar2 to CHAR)
  • Changing or adding the default value of a column (such as DEFAULT SYSDATE)
ALTER TABLE books
MODIFY (title VARCHAR2(10));
ALTER TABLE publisher
MODIFY (rating DEFAULT 'N');

Adding Primary Key Constraints

This makes customer# the primary key  the constraint name is used so you can easily 
identify an error if you try to enter a duplicate customer
If you don’t assign constraint names, the error message displays the
system-generated constraint name, which isn’t as helpful
ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);

DROP PRIMARY KEY

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Foreign Key Constraint

This command instructs Oracle 11g to add a FOREIGN KEY constraint on the Customer# column of the ORDERS table. The name chosen for the constraint is orders_customer#_fk. This constraint makes sure an entry for the Customer# column of the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS table. When the command executes, a message indicates the table was altered successfully

ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)

Unique Constraints

ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);

Check Constraint

ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);

Not Null Constraint

ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);

Creating NEW Tables

CREATE TABLE a_animals(
animal_id NUMBER(6),
 name VARCHAR2(25),
license_tag_number NUMBER(10),
admit_date DATE CONSTRAINT ail_admit_date_nn NOT NULL,
adoption_id NUMBER(5),
vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,
  CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
  CONSTRAINT ail_license_pk UNIQUE(license_tag_number)
);

Default Values

CREATE TABLE acctmgr(
id CHAR(4),
am_date DATE DEFAULT SYSDATE,
am_count NUMBER(7,2) DEFAULT 0,

);

Creating a table based on another table (subquery)

CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);

ADDING COMMENTS TO COLUMNS

PROBLEMS IS THE TABLE.DESCRIPTION IS THE COLUMN

COMMENT ON COLUMN problems.description IS 'Short description of the problem';

Back To Top- Home - Category