ORACLE CTAS(create table as select)使用注意點
看到這篇文章Beware of default values when using CTAS，關于create table as select
(CTAS)值得注意的地方：使用這條sql建立的表不會帶預設值。
操作以下實驗證明之:
[email protected]> create table p
2 ( id number primary key ,
3 username varchar(25) ,
4 passwd varchar(24),
5 email varchar(30),
6 birth date,
7 tel number ,
8 sex char(1));
表已建立。
[email protected]> alter table p add age number ;
表已更改。
建立一個唯一限制
[email protected]> alter table p modify tel unique;
表已更改。
建立一個檢查限制
[email protected]> alter table p add constraint p_ck_age check(age>0 and age<150);
表已更改。
建立一個預設限制
[email protected]> alter table p modify sex default '0';
表已更改。
[email protected]> desc user_constraints;
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
檢視原表中的各種限制資訊.
[email protected]> select constraint_name,constraint_type,status from user_constraints where table_name='P';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
SYS_C0015996 P ENABLED
SYS_C0015997 U ENABLED
P_CK_AGE C ENABLED
通過查詢建立相關的表資訊.
[email protected]> create table persion as select * from p;
表已建立。
[email protected]> select * from persion;
未標明行
[email protected]> insert into persion select * from p;
已建立0行。
檢查新表中的限制資訊
[email protected]> select constraint_name,constraint_type,status from user_constraints where table_name='PERSION';
未標明行
再次證明了使用CTAS存在一些問題.
Oracle查詢了一下文檔(11gr1)，關于 AS subquery 裡面叙述到
Oracle Database automatically defines on columns in the new table any NOT NULL constraints that were explicitly
created on the corresponding columns of the selected table if the subquery selects the column rather than an expression
containing the column. If any rows violate the constraint, then the database does not create the table and returns
an error.
顯示的NOT NULL限制自動會帶到新表。
NOT NULL constraints that were implicitly created by Oracle Database on columns of the selected table (for example,
for primary keys) are not carried over to the new table.
隐式的NOT NULL限制不會帶到新表，如主鍵。
In addition, primary keys, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column
default values are not carried over to the new table.
另外，主鍵，唯一，外鍵，check限制，分區，索引以及列的預設值不會帶到新表。
If the selected table is partitioned, then you can choose whether the new table will be partitioned the same way,
partitioned differently, or not partitioned. Partitioning is not carried over to the new table. Specify any desired
partitioning as part of the CREATE TABLE statement before the AS subquery clause.
在新表上可以選擇是否像像舊表那樣分區，或者不同的分區形式，或者建立非分區表。在AS subquery句之前指定。