Beware of CREATETABLEAS

For DBAs, CREATETABLEAS is a common practice. Although this method is simple, negligence can easily lead to unexpected problems. I was there a while ago.

For DBAs, it is common to create table. Although this method is simple, negligence can easily lead to unexpected problems. I was there a while ago.

For DBAs, it is common to create table. Although this method is simple, negligence can easily lead to unexpected problems. I encountered such a problem a while ago. Because the original TABLE is cloned and the data is stored in different tablespaces, we do not hesitate to use create table as. when running the package, the error...

-- 1. Non-empty constraint loss
--> Use create table as to create an object
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)

--> From the preceding desc, we can see that the newly created table has no non-null constraints.
--> Manually add a non-null constraint to the table, which is consistent with the original table. Of course, when using create table as, the index must be rebuilt separately.
Scott @ CNMMBO> alter table tb_dept modify (deptno not null );

Table altered.

Scott @ CNMMBO> drop table tb_dept; --> Delete the table tb_dept

Table dropped.

-- 2. If a non-empty constraint exists, the default constraint is lost.
--> Add non-null constraints to the loc column of the dept table and assign the default value.
Scott @ CNMMBO> alter table dept modify (loc default 'beijing' not null );

Table altered.

--> Add a record to the original table
Scott @ CNMMBO> insert into dept (deptno, dname) select 50, 'dev' from dual;

1 row created.

Scott @ CNMMBO> commit;

Commit complete.

--> The following query shows that the loc value of the newly added record 50 is the default value '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

--> Use create table as to create an object again
Scott @ CNMMBO> create table tb_dept as select * from dept;

Table created.

--> We can see from the following that, because the column loc has a default value, the not null constraint is assigned at the same time.
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;