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

最新推荐文章于 2021-09-20 08:07:32 发布

转载最新推荐文章于 2021-09-20 08:07:32 发布 · 382 阅读

本文探讨了在Oracle中使用CREATE TABLE AS SELECT (CTAS)时，默认值和约束的应用问题。CTAS创建的新表不会继承原表的默认值，但会自动携带原表显式定义的NOT NULL约束。此外，文章还介绍了如何手动设置新表的默认值。

看到这篇文章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.