?

?????????????????????? 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);

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;
create table t(id number);
insert?? into t values(1);
insert?? into t values(1);
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?

??????? 再enable and EXCEPTIONS INTO EXCEPTIONS
? ????下面是一個新建外鍵的樣例? ?

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))

????????????? DISABLE,

??? 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;

?

?

?