DDL for CONSTRAINTS and DEFAULTS

When using a CTAS to create a table from an existing table, the new table won't have all the constraints as the exsiting table except that the NOT NULL constraints are preserved. Below is an example showing a CHECK constraint is not presented in a table created from CTAS:

TESTDB> create table t (id number not null, val number check (val > 5));
 
Table created.
 
 
TESTDB> insert into t values(1,7);
 
1 row created.
 
TESTDB> commit;
 
Commit complete.
 
TESTDB> insert into t values (2,4);
insert into t values (2,4)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$MYUSRID.SYS_C001214102) violated
 
 
TESTDB> desc t;
 Name                  Null?    Type     
 --------------------- -------- --------
 ID                    NOT NULL NUMBER
 VAL                            NUMBER
 
TESTDB> create table t1 as select * from t;
 
Table created.
 
TESTDB> desc t1;
 Name                 Null?    Type
 -------------------- -------- ----------
 ID                   NOT NULL NUMBER
 VAL                           NUMBER
 
TESTDB> insert into t1 values (1, 4);
 
1 row created.
 


TESTDB> ;
  1* select owner, constraint_name, constraint_type, table_name, search_condition from user_constraints
TESTDB> /
 
OWNER        CONSTRAINT_NAME                C TABLE_NAME SEARCH_CONDITION
------------ ------------------------------ - ---------- ------------------------------
OPS$MYUSRID  SYS_C001214102                 C T          val > 5
OPS$MYUSRID  SYS_C001214101                 C T          "ID" IS NOT NULL
OPS$MYUSRID  SYS_C001214103                 C T1         "ID" IS NOT NULL

To create the constraints in the new table, I thus faced a task to extract all the DDL about constraints from the existing table. The approach I adopted after several tests was as follows.

1. First, I created a table my_constraints to have CLOB datatype for search_conditon column since we can not use LIKE operater on a LONG type column.

create table my_constraints ( 
    owner varchar2(30)
   , constraint_name varchar2(30)
   , constraint_type varchar2(1)
   , table_name varchar2(30)
   , search_condition clob
   ) ;

   insert into my_constraints
   select owner, constraint_name, constraint_type, table_name, to_lob(search_condition)
     from dba_constraints 
   where owner =&owner ;

2. For PK, UK and check constraints ( excluding NOT NULL), I used the following command to extract the DDL:

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', t.constraint_name , t.owner ) ddl_string
  from my_constraints t
 where table_name = upper('&table_name')
  and  owner=upper('&owner')
and constraint_type in ('P', 'U','C')
and ( search_condition is null
      or ( search is not null and search_condition not like '%NOT NULL')
    )
;

3. For FK constraints, I used the following command to extract the DDL

SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
  FROM (SELECT DISTINCT b.table_name, b.owner
           from dba_constraints b
           where b.constraint_type = 'R'
           AND b.owner = upper('&owner')
           AND b.table_name = upper('&table_name')) ;

CTAS also does not preserve DEFAULT values of columns. I used the following to obtain the 'ALTER TABLE' statement for DAFAULTS:

BEGIN
        FOR r IN (
             select owner, table_name, column_name, data_type, data_default
               from dba_tab_columns
             where owner = upper('&owner')
               and table_name = upper('&table_name')
               and data_default is not null
        )
        LOOP
                DBMS_OUTPUT.PUT_LINE
                ( 'ALTER TABLE ' || r.owner || '.' || r.table_name  ||
                 ' modify  ' ||r.column_name ||
                 ' default ' || r.data_default || ';');
        END LOOP;
END;
/

The reason we need to use PL/SQL instead of SQL is that DATA_DEFAULT column is LONG type. We can not use '||' with LONG type column.