当心 CREATE TABLE AS - 数据库编程

对 DBA 而言，CREATE TABLE AS 可谓是家常便饭，顺手拈来。需不知该方式虽然简单，但疏忽也容易导致意想不到的问题。笔者前阵子就碰上了这样的事情。由于是对原表进行克隆，且数据存储在不同的表空间，因此毫不犹豫地使用了CREATE TABLE AS，结果在运行package时，error...

--1、非空约束遗失
-->使用create table as 来创建对象
scott@CNMMBO> create table tb_dept as select * from dept where 1=0;

Table created.

scott@CNMMBO> desc dept;
Name Null Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

scott@CNMMBO> desc tb_dept;
Name Null Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

-->从上面的desc可以看出新创建的表少了非空约束
-->下面手动为其增加非空约束，增加后与原来的表是一致的。当然使用create table as时，索引是需要单独重建的。
scott@CNMMBO> alter table tb_dept modify (deptno not null);

Table altered.

scott@CNMMBO> drop table tb_dept; -->删除刚刚穿件的表tb_dept

Table dropped.

--2、存在非空约束时default约束遗失
-->下面为表dept的loc列添加非空约束，且赋予default值
scott@CNMMBO> alter table dept modify (loc default 'BeiJing' not null);

Table altered.

-->为原始表新增一条记录
scott@CNMMBO> insert into dept(deptno,dname) select 50,'DEV' from dual;

1 row created.

scott@CNMMBO> commit;

Commit complete.

-->下面的查询可以看到新增记录50的loc为缺省值'BeiJing'
scott@CNMMBO> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEV BeiJing

-->再次使用create table as来创建对象
scott@CNMMBO> create table tb_dept as select * from dept;

Table created.

-->从下面可知，由于列loc存在default值，所以此时not null约束被同时赋予
scott@CNMMBO> desc tb_dept
Name Null Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC NOT NULL VARCHAR2(13)

scott@CNMMBO> select * from tb_dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEV BeiJing

-->为新创建的表新增记录
-->新增时发现尽管not null约束生效，但原表上设定的default值不存在了
scott@CNMMBO> insert into tb_dept(deptno,dname) select 60,'HR' from dual;
insert into tb_dept(deptno,dname) select 60,'HR' from dual
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TB_DEPT"."LOC")

scott@CNMMBO> drop table tb_dept;

Table dropped.

--3、唯一约束遗失
scott@CNMMBO> alter table dept modify (dname unique);

Table altered.

scott@CNMMBO> create table tb_dept as select * from dept;

Table created.

scott@CNMMBO> insert into tb_dept select 60,'DEV','ShangHai' from dual;

1 row created.

scott@CNMMBO> commit;

Commit complete.

scott@CNMMBO> select * from tb_dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEV BeiJing
60 DEV ShangHai

-->有关check约束与外键约束不再演示

--4、最彻底的解决办法
scott@C

当心 CREATE TABLE AS(一)