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

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

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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?

Thanks.

On Tue, Mar 25, 2008 at 1:40 PM, Robert Freeman <robertgfreeman_at_yahoo.com> 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_at_gmail.com>
> To: Oracle-L Freelists <oracle-l_at_freelists.org>
> 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


--
http://www.freelists.org/webpage/oracle-l