11 Replies Latest reply: Aug 25, 2013 2:13 AM by Lalit Kumar B RSS

    Copy table Structure

    JP Rao

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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(唐波)

                            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

                              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.