Be careful when creating TABLE

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;

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

--> Add a record for the newly created table
--> When adding a table, it is found that although the not null constraint takes effect, the default value set in the original table does not exist.
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. The unique constraint is lost.
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 constraints and foreign key constraints are not demonstrated.

-- 4. the most thorough solution
Scott @ CNMMBO> select dbms_metadata.get_ddl ('table', 'dept') from dual;

DBMS_METADATA.GET_DDL ('table', 'dept ')
--------------------------------------------------------------------------------

Create table "SCOTT". "DEPT"
("DEPTNO" NUMBER (2, 0 ),
"DNAME" VARCHAR2 (14 ),
"LOC" VARCHAR2 (13) DEFAULT 'beijing' not null enable,
CONSTRAINT "PK_DEPT" primary key ("DEPTNO ")
Using index pctfree 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT)
TABLESPACE "GOEX_USERS_TBL" ENABLE,
UNIQUE ("DNAME ")
Using index pctfree 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT)
TABLESPACE "GOEX_USERS_TBL" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT)
TABLESPACE "GOEX_USERS_TBL"

-- 5. Demo Environment
Scott @ CNMMBO> select * from v $ version where rownum <2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0-64bit Production

-- 6. Demonstration conclusion
--> Although create table as clones tables and data, the data is complete, but the structure is only partially cloned.
--> Create table as will use the constraints of the table to be lost or out of an abnormal state.
--> When you create table as, indexes and triggers on the table will not be cloned at the same time.
--> Create table as is only used for testing. To obtain the complete structure statement, use the dbms_metadata.get_ddl package