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

From: Peter Teoh <htmldeveloper_at_gmail.com>
Date: Tue, 25 Mar 2008 14:02:55 +0800
Message-ID: <804dabb00803242302i5064c246q1690e7145ff24411@mail.gmail.com>


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
Received on Tue Mar 25 2008 - 01:02:55 CDT

Original text of this message