Re: CTAS only recreate non-NULL contrainsts - WHY?

From: Robert Freeman <>
Date: Mon, 24 Mar 2008 22:40:33 -0700 (PDT)
Message-ID: <>

Well, think about it.

If I do a CTAS which is a join between two tables, then which primary key gets moved over? You can define the primary key in the CTAS command if you need to like this:

 create table newemp (empid primary key)  as select empid from emp;

With respect to FK's, the same logic applies.... you could be creating rows that would violate the existing FK (say with an outer join).


Robert G. Freeman
Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
Blog: (Oracle Press)

  • Original Message ---- From: Peter Teoh <> To: Oracle-L Freelists <> Sent: Monday, March 24, 2008 10:33:26 PM Subject: CTAS only recreate non-NULL contrainsts - WHY?

It is known that CTAS only recreate the constraints if it is non-NULL, while others like primary key, foreign keys are all not created.

Is there any reason why?


-- Received on Tue Mar 25 2008 - 00:40:33 CDT

Original text of this message