oracle 表結構的非完全複製

今天有同事在問 複製表的時候 會把表的 not null 屬性也複製過來，而他要求對複製過來的欄位不加not null 欄位

SQL> desc DW_ACCOUNT
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------

 ACCT_ID                                   NOT NULL NUMBER(12)
 ACCT_CD                                            VARCHAR2(100)
 AREA_ID                                            NUMBER(5)
 AREA_NAME                                          VARCHAR2(200)
 PARTY_ID                                           NUMBER(12)
 ACCT_STATUS_CD                                     NUMBER(5)
 ACCT_STATUS                                        VARCHAR2(200)
 ACCT_STATUS_REASON_CD                              NUMBER(5)
 CREDIT_CLASS_CD                                    NUMBER(5)
 LIMIT_QTY                                          NUMBER(12)
 PROD_ID                                            NUMBER(12)
 ACCT_TYPE_CD                                       NUMBER(5)
 START_DT                                           DATE
 IF_FREE                                            VARCHAR2(1)
 ACCT_NAME                                          VARCHAR2(200)
 RELATED_ACCT_ID                                    NUMBER(12)
 PAYMENT_ACCOUNT_ID                                 NUMBER(12)

這裡可以看到 ACCT_ID 是 NOT NULL的限制，我們這裡複製DW_ACCOUNT的表結構

SQL> create table zyp_test as select * from dw_account where 1=2;

表已建立。

SQL> desc zyp_test;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------

 ACCT_ID                                   NOT NULL NUMBER(12)
 ACCT_CD                                            VARCHAR2(100)
 AREA_ID                                            NUMBER(5)
 AREA_NAME                                          VARCHAR2(200)
 PARTY_ID                                           NUMBER(12)
 ACCT_STATUS_CD                                     NUMBER(5)
 ACCT_STATUS                                        VARCHAR2(200)
 ACCT_STATUS_REASON_CD                              NUMBER(5)
 CREDIT_CLASS_CD                                    NUMBER(5)
 LIMIT_QTY                                          NUMBER(12)
 PROD_ID                                            NUMBER(12)
 ACCT_TYPE_CD                                       NUMBER(5)
 START_DT                                           DATE
 IF_FREE                                            VARCHAR2(1)
 ACCT_NAME                                          VARCHAR2(200)
 RELATED_ACCT_ID                                    NUMBER(12)
 PAYMENT_ACCOUNT_ID                                 NUMBER(12)

可以看到 NOT NULL的屬性也隨之被複制過來了，如果能夠在複製的時候 不復制NOT NULL的屬性呢？
SQL> drop table zyp_test;

表已刪除。
SQL> purge table zyp_test;

表已清除。

SQL> create table zyp_test (acct_id null, acct_cd not null) as select acct_id,ac
ct_cd from dw_account where 1=2;

表已建立。

SQL> desc zyp_test;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------

 ACCT_ID                                            NUMBER(12)
 ACCT_CD                                   NOT NULL VARCHAR2(100)

由於欄位太多我就不做完全的複製了，這裡其實可以發現 我們可以將源表的欄位屬性進行更改，NULL和NOT NULL都可以進行設定，當然如果在欄位後面不加NULL或者NOT NULL的話，複製的表就會完全繼承源表的欄位屬性。

那麼複製過來的原本非空（NOT NULL）的 會不會有值呢？

再次進行測試：

SQL> create table zyp_test (acct_id null, acct_cd not null) as select acct_id,ac
ct_cd from dw_account where rownum<2;

表已建立。

SQL> select * from zyp_test;

   ACCT_ID   ACCT_CD
--------------------------------------------------------------------------------

8.2000E+11   822818663

由此可以看到，欄位屬性發生改變不會對非空欄位修改為允許空 欄位造成影響，而如果將允許空欄位改為非空欄位，遇到null值就會報錯了。

SQL> CREATE TABLE zyp_test (acct_id null, credit_class_cd NOT NULL) AS SELECT ac
ct_id,credit_class_cd FROM DW_ACCOUNT WHERE ROWNUM<2;
CREATE TABLE zyp_test (acct_id null, credit_class_cd NOT NULL) AS SELECT acct_id
,credit_class_cd FROM DW_ACCOUNT WHERE ROWNUM<2

 *
第 1 行出現錯誤:
ORA-01400: 無法將 NULL 插入 ("STATA"."ZYP_TEST"."CREDIT_CLASS_CD")