CTAS都能带走源表的哪些属性

CREATE TABLE ... AS SELECT statement(CTAS):用CTAS语句可以根据一个存在的表创建一个新的表，而且能够根据对应的过滤条件将源表部分或者全部数据插入到新表当中。

今天用到CTAS，仔细看了一下文档，发现CTAS相关的内容其实并不简单。

假设现在有一个图书表：books,表结构如下：

 1 scott@UPDB> create table books(book_id number(6) primary key,book_name varchar2(20) not null,book_desc varchar2(200),book_price number(9,2)); 
 2 
 3 Table created.
 4 
 5 scott@UPDB> desc books
 6  Name                               Null?    Type
 7  ----------------------------------------------------- -------- ------------------------------------
 8  BOOK_ID                           NOT NULL NUMBER(6)
 9  BOOK_NAME                         NOT NULL VARCHAR2(20)
10  BOOK_DESC                             VARCHAR2(200)
11  BOOK_PRICE                            NUMBER(9,2)
12 
13 scott@UPDB>

现在要创建一个新的表new_book,结果要求和已经存在的表的结构一致，肯定用CTAS来创建：

 1 scott@UPDB> create table new_books as select book_id,book_name,book_desc,book_price from books;
 2 
 3 Table created.
 4 
 5 scott@UPDB> desc new_books
 6  Name                               Null?    Type
 7  ----------------------------------------------------- -------- ------------------------------------
 8  BOOK_ID                            　　　　　　NUMBER(6)
 9  BOOK_NAME                          NOT NULL  VARCHAR2(20)
10  BOOK_DESC                            　　　　 VARCHAR2(200)
11  BOOK_PRICE                            　　　　NUMBER(9,2)
12 
13 scott@UPDB>

有没有发现点什么？在源表中BOOK_ID是主键，现在new_books中BOOK_ID不是，下面来确定一下：

1 scott@UPDB> select constraint_name,constraint_type,table_name from user_constraints where table_name like '%BOOKS';
2 
3 CONSTRAINT_NAME            C TABLE_NAME
4 ------------------------------ - ------------------------------
5 SYS_C0011889               C BOOKS
6 SYS_C0011896               C NEW_BOOKS
7 SYS_C0011890               P BOOKS
8 
9 scott@UPDB>

确实在new_books中BOOK_ID已经不在是主键。在new_books中只有一个非空主键。在CTAS不能将主键约束传递到新表中。

现在将books表更新一下，将book_desc设置默认值为"very good"：

1 scott@UPDB> alter table books modify(book_desc default 'very good');
2 
3 Table altered.

验证默认值设置：

 1 scott@UPDB> select * from books;
 2 
 3    BOOK_ID BOOK_NAME        BOOK_DESC         BOOK_PRICE
 4 ---------- -------------------- -------------------- ----------
 5      1 C#            GOOD             ##########
 6 
 7 scott@UPDB> insert into books (book_id,book_name,book_price) values (2,'JAVA',8.3);
 8 
 9 1 row created.
10 
11 scott@UPDB> select * from books;
12 
13    BOOK_ID BOOK_NAME        BOOK_DESC         BOOK_PRICE
14 ---------- -------------------- -------------------- ----------
15      1 C#            GOOD             ##########
16      2 JAVA         very good         ##########

将new_books表删除，

1 scott@UPDB> drop table new_books;
2 
3 Table dropped.

然后在创建new_books表：

 1 scott@UPDB> create table new_books as select * from books;
 2 
 3 Table created.
 4 
 5 scott@UPDB> desc new_books
 6  Name                               Null?    Type
 7  ----------------------------------------------------- -------- ------------------------------------
 8  BOOK_ID                            NUMBER(6)
 9  BOOK_NAME                           NOT NULL VARCHAR2(20)
10  BOOK_DESC                            VARCHAR2(200)
11  BOOK_PRICE                            NUMBER(9,2)

验证默认值在新表中是否有作用：

 1 scott@UPDB> insert into new_books(book_id,book_name,book_price) values (1,'C#',8.5);
 2 
 3 1 row created.
 4 
 5 scott@UPDB> select * from new_books;
 6 
 7    BOOK_ID BOOK_NAME        BOOK_DESC         BOOK_PRICE
 8 ---------- -------------------- -------------------- ----------
 9      1 C#                    GOOD             ##########
10      2 JAVA                 very good         ##########
11      1 C#                                     ##########
12 
13 scott@UPDB>

在新创建的new_book中的book_desc没有默认值选项。所以在CTAS中也不能传递默认值。

将new_books表再次删除，将books表中的数据清除，在books中添加唯一性约束在尝试一下：

1 scott@UPDB> drop table new_books;
2 
3 Table dropped.
4 
5 scott@UPDB> delete from books;
6 
7 2 rows deleted.
8 
9 scott@UPDB>

给books表中的book_name设置唯一性索引，并查看：

 1 scott@UPDB> alter table books modify(book_name unique);
 2 
 3 Table altered.
 4 
 5 scott@UPDB> select constraint_name,constraint_type,table_name from user_constraints where table_name='BOOKS';
 6 
 7 CONSTRAINT_NAME            C TABLE_NAME
 8 ------------------------------ - ------------------------------
 9 SYS_C0011889               C BOOKS
10 SYS_C0011890               P BOOKS
11 SYS_C0011898               U BOOKS
12 
13 scott@UPDB>

在创建new_books表，在查看一下新表中的约束：

 1 scott@UPDB> create table new_books as select * from books;
 2 
 3 Table created.
 4 
 5 scott@UPDB> select constraint_name,constraint_type,table_name from user_constraints where table_name='NEW_BOOKS';
 6 
 7 CONSTRAINT_NAME            C TABLE_NAME
 8 ------------------------------ - ------------------------------
 9 SYS_C0011899               C NEW_BOOKS
10 
11 scott@UPDB>

此时新表new_books中，只有一个非空约束，在CTAS中唯一性约束也不能传递。

在books中添加一个索引,因为主键和唯一性约束的时候自动创建索引，查看books中的索引：

1 scott@UPDB> select index_name,table_name,column_name from user_ind_columns where table_name='BOOKS';
2 
3 INDEX_NAME         TABLE_NAME             COLUMN_NAME
4 -------------------- ------------------------------ --------------------
5 SYS_C0011890         BOOKS                BOOK_ID
6 SYS_C0011898         BOOKS                BOOK_NAME
7 
8 scott@UPDB>

再看new_books中的情况：

1 scott@UPDB> select index_name,table_name,column_name from user_ind_columns where table_name='NEW_BOOKS';
2 
3 no rows selected
4 
5 scott@UPDB>

在new_books中没有索引，所以CTAS也不能传递索引。

CTAS中只能够将表的列的结构、列名、数据类型、可选的行记录、单独创建的非空索引才能够传递到新表中，其他的都不包括。