当前位置：文江博客话题详情

如何制作带有 PK 的表格副本？

发布于 2024-10-31 19:23:19 字数 83 浏览 0 评论 0原文

在 Oracle 10g 数据库中，我想复制现有表。我希望它具有与原始表相同的数据和行。虽然原始表使用 PK，所以我不确定如何复制它并保持它们的唯一性。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

記柔刀 2024-11-07 19:23:19

oracle 将 pk 维护为列约束。您必须复制该表，然后为新表创建此约束。

以下代码说明了如何完成您的工作。

  -- 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 );

希望这有帮助，

最诚挚的问候，

卡斯滕

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

回复收藏 0 原文

愁杀 2024-11-07 19:23:19

您可以使用进行复制，

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

您也可以使用dbms_metadata.get_ddl来获取表的关联约束
并通过所有检查创建它

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

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;

回复收藏 0 原文

囚你心 2024-11-07 19:23:19

或者您可以在一条语句中完成所有操作：

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

我认为使用 create table as select 时指定列名称的格式有点繁琐，因为我不相信您可以指定数据类型（确实有点明显），但您可以指定诸如 not null、主键和外键之类的约束。

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.

回复收藏 0 原文

~没有更多了~