ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 注意create table as select 不会带默认值及相关

注意create table as select 不会带默认值及相关

原创 Linux操作系统 作者:zecaro 时间:2011-06-12 21:56:03 0 删除 编辑

        看到这篇文章Beware of default values when using CTAS,关于create table as select (CTAS)值得注意的地方:使用这条sql创建的表不会带默认值。

        如上面的文章中所说,可以在创建表之后,修改默认值:

alter table t2 modify (id number default (0));

      或者在创建的语句里给列加上默认值

create table t2(id default 1) as select * from t1;


       然后查询了一下文档(11gr1),关于 AS subquery 里面叙述到

  • Oracle Database automatically defines on columns in the new table any  NOT NULL  constraints that were explicitly created on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column. If any rows violate the constraint, then the database does not create the table and returns an error.

显示的NOT NULL约束自动会带到新表。

  • NOT NULL  constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.

隐式的NOT NULL约束不会带到新表,如主键。

  • In addition, primary keys, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column default values are not carried over to the new table.

另外,主键,唯一,外键,check约束,分区,索引以及列的默认值不会带到新表。

  • If the selected table is partitioned, then you can choose whether the new table will be partitioned the same way, partitioned differently, or not partitioned. Partitioning is not carried over to the new table. Specify any desired partitioning as part of the  CREATE TABLE  statement before the  AS subquery  clause.

在新表上可以选择是否像像旧表那样分区,或者不同的分区形式,或者创建非分区表。在AS subquery句之前指定。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23650854/viewspace-697781/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-10-28

  • 博文量
    182
  • 访问量
    349852