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');


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