This discussion is archived
11 Replies Latest reply: Aug 25, 2013 12:13 AM by Lalit Kumar B RSS

Copy table Structure

JP Rao Newbie
Currently Being Moderated

Hi

 

When a table structure is copied from another table.

What are the constraints copied and why?

Unique key

Primary key

Foreign Key

Check

Not null

 

Thanks in advance.

 

-Prakash

  • 1. Re: Copy table Structure
    sb92075 Guru
    Currently Being Moderated

    JPRao wrote:

     

    Hi

     

    When a table structure is copied from another table.

    What are the constraints copied and why?

    Unique key

    Primary key

    Foreign Key

    Check

    Not null

     

    Thanks in advance.

     

    -Prakash

    It depends.

     

    since SQL does not have any COPY command, what you get depends upon what exactly you do.

     

    How do I ask a question on the forums?

    https://forums.oracle.com/message/9362002#9362002

  • 2. Re: Copy table Structure
    rp0428 Guru
    Currently Being Moderated

    JPRao wrote:

     

    Hi

     

    When a table structure is copied from another table.

    What are the constraints copied and why?

    Unique key

    Primary key

    Foreign Key

    Check

    Not null

     

    Thanks in advance.

     

    -Prakash

    What result did you get when YOU copied another table? Post the results here.

     

    Don't be afraid of breaking Oracle by actually trying things.

  • 3. Re: Copy table Structure
    GPU Explorer
    Currently Being Moderated

    I am assuming you want to copy structure of one table to other. If you do that only not null check constraints copied to other table. Below is the example

     

    SQL> select * from v$version;

    BANNER                                                                         
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production   
    PL/SQL Release 11.2.0.2.0 - Production                                         
    CORE 11.2.0.2.0 Production                                                     
    TNS for Linux: Version 11.2.0.2.0 - Production                                 
    NLSRTL Version 11.2.0.2.0 - Production                 

                           

    SQL> drop table TableA_Copy;

    Table dropped.

    SQL> drop table TableA;

    Table dropped.

    SQL> drop table ParentTableA;

    Table dropped.

    SQL>
    SQL> CREATE TABLE ParentTableA (col1 NUMBER primary key);

    Table created.

    SQL>
    SQL> CREATE TABLE TableA
      2  (
      3     col1   NUMBER PRIMARY KEY,
      4     col2   NUMBER,
      5     col3   NUMBER NOT NULL,
      6     col4   NUMBER,
      7     CONSTRAINT chk_cons CHECK (col2 IN (1, 2)),
      8     CONSTRAINT fk_cons FOREIGN KEY (col4) REFERENCES ParentTableA (col1)
      9  );

    Table created.

    SQL>
    SQL> create table  TableA_Copy as select * from tableA where 1=2;

    Table created.

    SQL>
    SQL> select constraint_name, table_name, column_name  from all_cons_columns where table_name = 'TABLEA' order by column_name;

    CONSTRAINT_NAME       TABLE_NAME        COLUMN_NAME                                                                                   
    ---------------                      ----------                    -----------                                                                                   
    SYS_C00936298           TABLEA                        COL1                                                                                      
    CHK_CONS                    TABLEA                        COL2                                                                                      
    SYS_C00936296           TABLEA                        COL3                                                                                      
    FK_CONS                      TABLEA                        COL4                                                                                      

    SQL>
    SQL> select constraint_name, table_name, column_name  from all_cons_columns where table_name = 'TABLEA_COPY' order by column_name;

    CONSTRAINT_NAME  TABLE_NAME              COLUMN_NAME                                                                                  
    ----------------                -------------                   --------------                                                                               
    SYS_C00936300        TABLEA_COPY               COL3              

     

    Thanks,

    GPU                                                                        

  • 4. Re: Copy table Structure
    rp0428 Guru
    Currently Being Moderated

    I am assuming you want to copy structure of one table to other. If you do that only not null check constraints copied to other table.

    That may be ONE way to copy the structure of a table but as SB already said

    since SQL does not have any COPY command, what you get depends upon what exactly you do.

  • 5. Re: Copy table Structure
    Lalit Kumar B Explorer
    Currently Being Moderated

    GPU wrote:

     

    I am assuming you want to copy structure of one table to other. If you do that only not null check constraints copied to other table.

    And what happens when you do the same with COPY command in SQL*Plus.

  • 6. Re: Copy table Structure
    Solomon Yakobson Guru
    Currently Being Moderated

    LalitKumarB wrote:

     

    And what happens when you do the same with COPY command in SQL*Plus.

     

    SQL*Plus COPY command create mode creates table and not null constraints. Keep in mind, COPY command is "frozen in time". Check documentation:

     

    The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8i. The COPY command is likely to be deprecated in a future release.

     

    SY.

  • 7. Re: Copy table Structure
    969151 Newbie
    Currently Being Moderated

    Hi Rajesh,

     

    Create table table1 as (select * from table2)

     

     

    table1 - the table were you need the structure with data.

     

    table 2 - original table

     

     

    mark me if its helps you..

     

    Regards

    Kashif M

  • 8. Re: Copy table Structure
    Lalit Kumar B Explorer
    Currently Being Moderated

    SolomonYakobson wrote:

     

    LalitKumarB wrote:

     

    And what happens when you do the same with COPY command in SQL*Plus.

     

    SQL*Plus COPY command create mode creates table and not null constraints. Keep in mind, COPY command is "frozen in time". Check documentation:

     

    The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8i. The COPY command is likely to be deprecated in a future release.

     

    SY.

    Thanks SY for adding more meaningful information to my post.

    My intention was to share that even COPY in SQL*Plus would create table and not null constraints.

  • 9. Re: Copy table Structure
    Lalit Kumar B Explorer
    Currently Being Moderated

    969151 wrote:

     

    Hi Rajesh,

     

    Create table table1 as (select * from table2)

     

     

    table1 - the table were you need the structure with data.

     

    table 2 - original table

     

     

    mark me if its helps you..

     

    Regards

    Kashif M

    But this is not at all relevant to what OP has asked.

  • 10. Re: Copy table Structure
    botang(唐波) Explorer
    Currently Being Moderated

    Well,

    Only the not null defined at column level ,ie. create table t1 ( a number not null ) will be copied.

    The not null defined in table level must not  be copied , eg. create table t2 ( a  number , check (a is not null ).

  • 11. Re: Copy table Structure
    Lalit Kumar B Explorer
    Currently Being Moderated

    botang(唐波) wrote:

     

    Well,

    Only the not null defined at column level ,ie. create table t1 ( a number not null ) will be copied.

    The not null defined in table level must not  be copied , eg. create table t2 ( a  number , check (a is not null ).

     

    You are comparing NOT NULL constraint with CHECK constraint. So it is an ambiguous statement.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points