FAQ
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?

Search Discussions

  • Robert Freeman at Mar 25, 2008 at 5:40 am
    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
    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?
  • Peter Teoh at Mar 25, 2008 at 6:02 am
    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
    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
    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?


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




    --
    Regards,
    Peter Teoh
    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark D at Mar 25, 2008 at 5:07 pm
    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
    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
    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
    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?


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




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

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 25, '08 at 4:33a
activeMar 25, '08 at 5:07p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase