Oracle Programming/Using DDL Statements to Create and Manage Tables

From Wikibooks, open books for an open world
Jump to: navigation, search

Categorizing the main database objects[edit]

Reviewing the table structure[edit]

Listing the data types that are available for columns[edit]

Creating a simple table[edit]

create table [table_name] as colunmname1,colunmname2,..........;

Explaining how constraints are created at the time of table creation[edit]

Concept In essence, constraints safeguard and validate the data.

Primary Key and Unique constraints both ensure the data is not duplicated. Primary Key also ensure the data is not null. Oracle will automatically generate index for Primary Key and Unique constraints. A table can only have one Primary Key, but it can have multiple unique constraints.

Foreign Key ensure the data exists in the column of the parent table it refer to. Each parent record can have multiple child records, but each child can relate to ONLY one parent record. A column with Foreign Key may not necessary to have an index.


Foreign Key can only refer to column with Primary Key or Unique Constraint

   create table tblA (colX number, colY char);
   create table tblB (colX number);
   alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
   -- ORA-02270: no matching unique or primary key for this column-list
   alter table tblA add (constraint colX_PK primary key (colX));
   alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
   -- alter table success.


A table can ONLY have one Primary Key, but it can have multiple UNIQUE key. if the child table(s) require to referencing column other than primary key, the column on the parent table must have UNIQUE constraint.

   alter table tblA add (constraint colY_PK primary key (colY));
   -- ORA-02260: table can have only one primary key

Cannot create PK or Unique on a column contains duplicate data

   insert into tblA values(1,'A');
   insert into tblA values(2,'A');
   alter table tblA add (constraint colY_UK unique (colY));
   -- ORA-02299: cannot validate (HR.COLY_UK) - duplicate keys found


   delete from tblA where colx = 2;
   alter table tblA add (constraint colY_UK unique (colY));
   -- alter table success.
   create table tblC (colY char);
   alter table tblC add (constraint colY_FK foreign key (colY) references tblA(colY));
   -- alter table success.


Insert data into a column with FK, the value must already exist in the column that the FK reference to.

   insert into tblC values ('B');
   -- ORA-02291: integrity constraint (HR.COLY_FK) violated - parent key not found
   insert into tblC values ('A');
   -- 1 rows inserted


As long as a foreign key exist, the parent table can truncate/delete the data or disable the PK or Unique constraint

   truncate table tblA;
   -- ORA-02266: unique/primary keys in table referenced by enabled foreign keys


Find out the constraint information in Oracle

   desc all_constraints;
   select
     a.owner, a.table_name, a.constraint_name,
     a.constraint_type, a.status, a.r_owner, a.r_constraint_name,
     b.table_name as r_table_name, b.status as r_status
   from all_constraints a
     left join all_constraints b on a.owner = b.owner and a.r_constraint_name = b.constraint_name
   where a.table_name like 'TBL%';
   select *
   from all_cons_columns
   where table_name like 'TBL%';

Disable constraint that have foreign key refer to is not allowed, in order to do this, you have to disable the foreign key first.

   alter table tblA disable constraint colX_PK;
   -- ORA-02297: cannot disable constraint (HR.COLX_PK) - dependencies exist
   alter table tblA disable constraint colY_UK;
   -- ORA-02297: cannot disable constraint (HR.COLY_UK) - dependencies exist
   alter table tblC disable constraint colY_FK;
   alter table tblB disable constraint colX_FK;
   alter table tblA disable constraint colX_PK;
   alter table tblA disable constraint colY_UK;
   truncate table tblA;

If the data in parent table is deleted, re-enable the foreign key that contain data reference to the missing data is not allowed.

   select * from tblC;
   alter table tblA enable constraint colY_UK;
   alter table tblC enable constraint colY_FK;
   -- ORA-02298: cannot validate (HR.COLY_FK) - parent keys not found

Generate a sql statements to disable all the Foreign Key on a specified table

   select
     'alter table '||a.owner||'.'||a.table_name||
     ' disable constraint '||a.constraint_name||';' as STMT
   from all_constraints a, all_constraints b
   where a.constraint_type = 'R'
     and a.r_constraint_name = b.constraint_name
     and a.r_owner = b.owner
     and b.table_name = 'TBLA';

Describing how schema objects work[edit]