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?

Thanks.

On Tue, Mar 25, 2008 at 1:40 PM, Robert Freeman
<robertgfreeman@xxxxxxxxx> wrote:
> 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).
>
>  RF
>
>  Robert G. Freeman
>  Author:
>  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: http://robertgfreeman.blogspot.com (Oracle Press)
>
>
>
>  ----- Original Message ----
>  From: Peter Teoh <htmldeveloper@xxxxxxxxx>
>  To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
>  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?
>
>
>  --
>  http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>



-- 
Regards,
Peter Teoh
--
http://www.freelists.org/webpage/oracle-l

Other related posts: