Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

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

 

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.

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks