CTAS and NOT NULL

Article by Rampant author Laurent Schneider

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.