CTAS and NOT NULL

When you create a table as select (CTAS), you lose a lot of information like grants, partitioning, organization, referential integrity, check constraints. But the NOT NULL constraints remain. Sometimes

Let’s see when the not null constraints are not copied to the new table.

Here is the test case :
create table lsc_t1(
c0 number constraint C_PRIMARY_KEY primary key,
c1 number constraint C_DEFERRABLE not null deferrable,
c2 number constraint C_NOVALIDATE not null novalidate,
c3 number constraint C_DISABLE not null disable,
c4 number constraint C_DISABLE_VALIDATE not null disable validate,
c5 number constraint C_NOT_NULL not null
);
create table lsc_t2 as select * from lsc_t1;

let’s describe the tables :
SQL> desc lsc_t1
Name Null? Type
----------------------------- -------- --------------------
C0 NOT NULL NUMBER
C1 NUMBER
C2 NUMBER
C3 NUMBER
C4 NOT NULL NUMBER
C5 NOT NULL NUMBER

SQL> desc lsc_t2
Name Null? Type
----------------------------- -------- --------------------
C0 NUMBER
C1 NUMBER
C2 NUMBER
C3 NUMBER
C4 NOT NULL NUMBER
C5 NOT NULL NUMBER

The NOT NULL of c0 has been lost. C0 is the primary key, and the primary key is not transferred to the target table. We can see the non-deferrable validated not-null constraints c4 and c5.

Let’s compare all the constraints :
select * from user_constraints where table_name in ('LSC_T1','LSC_T2');

  LSC_T1 LSC_T2
C0 PRIMARY KEY

 
C1 DEFERRABLE

 
C2 ENABLED NOT VALIDATED

 
C3 DISABLED NOT VALIDATED

 
C4 DISABLED VALIDATED

ENABLED VALIDATED
C5 ENABLED VALIDATED

ENABLED VALIDATED

The deferrable and not-validated check constraints are lost. The regular ENABLE VALIDATE constraint is ok, but the DISABLE VALIDATE constraint is now enabled.

8 thoughts on “CTAS and NOT NULL

  1. Brian Tkatch

    Interesting. I didn’t know CTAS copied any CONSTRAINTs. After all, the SELECT itself has no CONSTRAINTs.

  2. Pingback: Blogroll Report 10/07/2009 – 17/07/2009 « Coskan’s Approach to Oracle

  3. Vikas Tawde

    Thanks for the info. But if you use select in CTAS then not null constraints also doesnt copy. only create table as select * from copy not null constraint. I am trying to understand what is the fastest way to copy the constraint & data.

  4. Vikas Tawde

    Oops.. some text is missing from my above reply. select * from table only copies not null in ctas. But if you use select column names from table then it doesnt copy.

Comments are closed.