Beware of CREATETABLEAS

Source: Internet
Author: User
Tags dname
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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.