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. Interesting. I didn’t know CTAS copied any CONSTRAINTs. After all, the SELECT itself has no CONSTRAINTs.

  2. 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.

  3. 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.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>