oracle 如何用PK制作表格的副本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5627636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I make a copy of a table with a PK?
提问by Ferret
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.
在 Oracle 10g 数据库中，我想复制现有表。我希望它与原始表具有相同的数据和行。原始表虽然使用 PK，所以我不确定如何复制它并保持它们的唯一性。
回答by collapsar
oracle maintains the pk as a column constraint. you have to copy the table and subsequently create this constraint for the new table.
oracle 维护 pk 作为列约束。您必须复制该表，然后为新表创建此约束。
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
卡斯滕
回答by V4Vendetta
You can make the copy using
您可以使用
CREATE TABLE dummy_copy as SELECT * FROM dummy//Structure and data
Also you could use dbms_metadata.get_ddlto get the associated constraints of the table and create it with all the checks
您也可以使用dbms_metadata.get_ddl来获取表的关联约束并使用所有检查创建它
SELECT dbms_metadata.get_ddl( 'TABLE', 'dummy' ) FROM DUAL;
回答by Mike Meyers
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 selectis 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.
我认为在使用时指定列名的格式create table as select有点繁琐，因为我不相信您可以指定数据类型（确实很明显），但您可以指定约束，例如not null主键和外键。
