Why should Oracle assume that just because the source table has a PK or
even just an index defined on it that you would want the index on the
new table? In many cases I have not wanted the existing indexes or
constraints on the copy. If I need these objects I can use DDL to add
Mark D Powell --
Phone (313) 592-5148
On Behalf Of Peter Teoh
Sent: Tuesday, March 25, 2008 2:03 AM
To: Robert Freeman
Cc: Oracle-L Freelists
Subject: Re: CTAS only recreate non-NULL contrainsts - WHY?
Thank you Robert. So I guessed it will ALWAYS be true that NON-NULL
constraints will never be violated, have to think about it :-).
But on the other hand, if CTAS is on only one table, can't Oracle be
smart enough to recreate the index at the same time - if the columns
included in the CTAS statement is a superset of the columns that make up
the index of the table?
As a special case, if it is just a simple "create table as select * from
mytable" then simply just recreate all the index and constraints.
Will this lead to any problems?
On Tue, Mar 25, 2008 at 1:40 PM, Robert Freeman
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
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
----- 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?