ORACLE CTAS(create table as select)使用注意点
看到这篇文章Beware of default values when using CTAS，关于create table as select
(CTAS)值得注意的地方：使用这条sql创建的表不会带默认值。
操作以下实验证明之:
[email protected]> create table p
2 ( id number primary key ,
3 username varchar(25) ,
4 passwd varchar(24),
5 email varchar(30),
6 birth date,
7 tel number ,
8 sex char(1));
表已创建。
[email protected]> alter table p add age number ;
表已更改。
创建一个唯一约束
[email protected]> alter table p modify tel unique;
表已更改。
创建一个检查约束
[email protected]> alter table p add constraint p_ck_age check(age>0 and age<150);
表已更改。
创建一个默认约束
[email protected]> alter table p modify sex default '0';
表已更改。
[email protected]> desc user_constraints;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
查看原表中的各种约束信息.
[email protected]> select constraint_name,constraint_type,status from user_constraints where table_name='P';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
SYS_C0015996 P ENABLED
SYS_C0015997 U ENABLED
P_CK_AGE C ENABLED
通过查询创建相关的表信息.
[email protected]> create table persion as select * from p;
表已创建。
[email protected]> select * from persion;
未选定行
[email protected]> insert into persion select * from p;
已创建0行。
检查新表中的约束信息
[email protected]> select constraint_name,constraint_type,status from user_constraints where table_name='PERSION';
未选定行
再次证明了使用CTAS存在一些问题.
Oracle查询了一下文档(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句之前指定。