ORACLE中CONSTRAINT的四對屬性

                       ORACLE中CONSTRAINT的四對屬性

summary:在data migrate時,某些表的限制總是困擾着我們,讓我們的migratet舉步維艱,怎樣利用限制本身的屬性來處理這些問題呢?本文具體介紹了限制的四對屬性: Deferrable/not deferrable, Deferred/immediate, enalbe/disable, validate/novalidate,以及怎樣應用這些屬性靈活應對相關問題

1.     Deferrable,not deferrable(default value)

1)      這對屬性是定義能否夠defer,defer是指作檢查的時機,假設在commit的時check為Defer,否則為immediate .僅僅有在設定Deferrable才幹夠設定還有一個屬性2-- Deferred,immediate.

2)      設定defer check的方法有兩種(前提是建立了Deferrable的contraint)

a.       通過建contraint時指定Deferred值

b.      通過會話級别的語句改動

           SET CONSTRAINT(s) contraint_name/all  immediate/deferred.

3)      這對屬性是在建立的constraint的時候定義的,不能被改動.

4)  notice:假設建立了Deferrable的uk或pk,僅僅會建立對應的nonuniquce index,而不會建立uniquce index   

2.     Deferred,immediate(default value)  

1)      這對屬性定義是否defer.   Deferred: check on commit; immediate: check immediate.

2)      If constraint is not deferrable,immediate is only choice.

3)      For  example:

CREATE TABLE games

  (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores)

   INITIALLY DEFERRED DEFERRABLE);

insert into games values(1);

commit;--在此報錯

You will not get a error util you commit it;

SET CONSTRAINT(s) unq_num immediate;--改動屬性

insert into games values(2);

insert into games values(2);--在此報錯

commit;

You will get a error when you execute the second sql;

3.      novalidate, validate(default value)

1)      這對屬性定義constraint是否對表中已經存在的資料作檢查,比如:

create table t(id number);

insert   into t values(1);

insert   into t values(2);

alter table t add constraint ch_100 check(id>=100);  --失敗

alter table t add constraint ch_100 check(id>=100) novalidate;--成功

2)      notice:與唯一索引相關的contraint(比如pk,uk),要做到以上的效果還必須設定為Deferrable(僅僅是建立非唯一性索引),由于在維護索引是,假設違反了唯一性也會報錯,是以必須建立非唯一性索引.比如:

drop table t;

alter table t add constraint ch_100 unique(id) ; --報錯

alter table t add constraint ch_100  unique(id) novalidate; --報錯

alter table t add constraint ch_100  unique(id)  deferrable  novalidate;--成功

4.     disable, enalbe(default value)      

1)      啟用和禁用constraint.在建立pk和uk時定義了disable,将不建立對應的索引.

ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;

ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;

alter table t add constraint ch_100  unique(id) disable;

2)      DISABLE  uk或pk作了些什麼:

Disable非deferrable 的pk、uk,将删除對應的索引(除非指定了keep index,可是keep下來的索引是唯一性的,insert資料時還是要作唯一性檢查的),在enable時重建索引.

Disbale  deferrable 的pk、uk将保留原來的索引(由于原來的索引就是非唯一性的,不影響insert的操作).

3)      一些操作經驗

KEEP INDEX要注意的:

  a.ALTER TABLE games  DISAble  CONSTRAINT fk_num keep index;--唯一索引被保留,是以還是不能插入反複的資料.不應該keep index.

  b.ALTER TABLE games  DISAble  CONSTRAINT fk_num;--假設上一步被運作,那麼此語句什麼都不做,唯一索引仍被保留,此時應該先enable在disable.假設原來的狀态是able的話,那麼唯一索引将被删除.

5.    報告限制異常

    假設校驗限制時存在異常，則傳回一個錯誤，且完整性限制仍保持未被校驗狀态。當限制存在異常時，一個語句就不能正确運作，則此語句被復原。假設存在異常，必需要更新或删除了限制的全部異常後，才幹夠校驗限制。可是在使用ALTER TABLE語句不能确定哪一行違反限制，為了确定哪一行，在公布ENABLE子句中帶有EXCEPTION選項的ALTER TABLE語句。

    EXCEPTION選項将ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一個指定的表中。在啟用限制前，硬建立一個合适的異常報告表，用來接收ENABLE子句的EXCEPTION選項資訊，能夠直接運作'?\rdbms\admin\utlexcpt.sql'或'?\rdbms\admin\utlexcpt1.sql'腳本來進行建立。注意：這兩個腳本的差别在于資料庫的相容性級别和所分析的表的類型。

    使用的文法例如以下：

    ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;

 或者

    alter table team disable validate   constraint id_num   EXCEPTIONS INTO EXCEPTIONS;--這種方法不建立索引

    假設存在異常，則直接查詢 SELECT * FROM EXCEPTIONS; 就可以。假設須要更加具體的資訊，則能夠使用ROWID與原表的ROWID進行關聯，這樣就能夠查出原始表中目前行的全部資訊，并進行改動。

怎樣利用EXCEPTIONS來處理實踐中的一些問題：

（1）在已有的constraint上載入資料

        先 disable

        再載入

        再enable and EXCEPTIONS INTO EXCEPTIONS

     再依據EXCEPTIONS表的紀錄delete

      最後enable

（2）建立constraint,原有的資料違反了這個限制

        先create disable 

      下面是一個建立外鍵的樣例   

alter table team add constraint id_num foreign key(id)  references games(scores) INITIALLY disable; 

   alter table team enable   constraint id_num   EXCEPTIONS INTO EXCEPTIONS;           select * from EXCEPTIONS;

6.     關于contraint操作的一些腳步

定義限制

         alter table t add constraint ch_100  unique(id) disable  ;

   alter table t add constraint ch_100  unique(id);

        alter table t add constraint ch_100  unique(id) deferred deferrable novalidate;

CREATE TABLE dept_20

   (employee_id     NUMBER(4),  

    commission_pct  NUMBER(7,2),

    department_id,

   CONSTRAINT fk_deptno

      FOREIGN  KEY (department_id)

      REFERENCES  departments(department_id)

      on delete/set null cascade);

ALTER TABLE dept_20

   ADD CONSTRAINT fk_empid_hiredate

   FOREIGN KEY (employee_id, hire_date)

   REFERENCES hr.job_history(employee_id, start_date)

   EXCEPTIONS INTO EXCEPTIONS;

CREATE TABLE divisions 

   (div_no    NUMBER  CONSTRAINT check_divno

              CHECK (div_no BETWEEN 10 AND 99)

              DISABLE,

    div_name  VARCHAR2(9)  CONSTRAINT check_divname

              CHECK (div_name = UPPER(div_name))

    office    VARCHAR2(10)  CONSTRAINT check_office

              CHECK (office IN ('DALLAS','BOSTON',

              'PARIS','TOKYO'))

              DISABLE);

改動或删除現有的限制

    注：在使用外鍵參考了PRIMARY或UNIQUE鍵時，不能停用或删除被參考限制

    延遲校驗的啟用停用：

    SET CONSTRAINT(s) unq_num/all immediate;

      SET CONSTRAINT(s) unq_num/all deferred;

    停用：

    ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;

    ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,

                     DISABLE UNIQUE (dname, loc) KEEP INDEX;

    啟用非校驗：

    ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;

    ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,

                     ENABLE NOVALIDATE UNIQUE (dname, loc);

    啟用校驗：

    ALTER TABLE dept MODIFY CONSTRAINT dname_ukey VALIDATE;

    ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;

    若要停用/删除相關的FOREIGN KEY限制，則：

    ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;

    删除：

    ALTER TABLE dept DROP UNIQUE (dname, loc);

    ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX,

                    DROP CONSTRAINT dept_fkey;