How can I make a copy of a table with a PK?

Question

In an Oracle 10g database, I would like to make a copy of an existing table. I would like it to have the same data and rows as the original table. The original table uses a PK though, so I'm not sure how to copy it and keep them unique.

Answer 1

You can make the copy using

CREATE TABLE dummy_copy as SELECT * FROM dummy//Structure and data

Also you could use dbms_metadata.get_ddl to get the associated constraints of the table and create it with all the checks

 SELECT dbms_metadata.get_ddl( 'TABLE', 'dummy' ) FROM DUAL;

Answer 2

oracle maintains the pk as a column constraint. you have to copy the table and subsequently create this constraint for the new table.

the following code illustrates how to get your job done.

  -- setting up table t1 - this is just for the sake of demonstration
  create table t1 (
        t_id  integer
      , t_data   varchar2(40)
  );
  alter table t1 modify ( t_id constraint t1_pk primary key );

  insert into t1 values ( 1, 'test');
  insert into t1 values ( 2, 'another test');
  insert into t1 values ( 3, 'final test');
  commit;

  -- copying table t1 (definition + contents) and defining the pk
  create table t2 as ( select * from t1 );
  alter table t2 modify ( t_id constraint t2_pk primary key );

hope this helps,

best regards,

carsten

Answer 3

Or you can just do it all in one statement:

create table mike_temp_1 (
  col1,
  col2,
  col3,
  col4,
  col5,
  constraint xpk_mike_temp_1 primary key (col1)
)
as select *
from OLD_POLICY_TERM;

I think the format of specifying column names when using create table as select is a bit fiddly in that I don't believe that you can specify data types (sort of obvious really) but you can specify constraints such as not null, primary key and foreign key.