?????????????????????? ORACLE中CONSTRAINT的四對屬性
summary:在data migrate時,某些表的約束總是困擾著我們,讓我們的migratet舉步維艱,怎樣利用約束本身的屬性來處理這些問題呢?本文具體介紹了約束的四對屬性: Deferrable/not deferrable, Deferred/immediate, enalbe/disable, validate/novalidate,以及怎樣應(yīng)用這些屬性靈活應(yīng)對相關(guān)問題
1.???? Deferrable,not deferrable(default value)
1)????? 這對屬性是定義能否夠defer,defer是指作檢查的時機,假設(shè)在commit的時check為Defer,否則為immediate .僅僅有在設(shè)置Deferrable才干夠設(shè)置還有一個屬性2-- Deferred,immediate.
2)????? 設(shè)置defer check的方法有兩種(前提是建立了Deferrable的contraint)
a.?????? 通過建contraint時指定Deferred值
b.????? 通過會話級別的語句改動
?????????? SET CONSTRAINT(s) contraint_name/all ?immediate/deferred.
3)????? 這對屬性是在創(chuàng)建的constraint的時候定義的,不能被改動.
4)? notice:假設(shè)建立了Deferrable的uk或pk,僅僅會建立對應(yīng)的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是否對表中已經(jīng)存在的數(shù)據(jù)作檢查,比如:
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:與唯一索引相關(guān)的contraint(比如pk,uk),要做到以上的效果還必須設(shè)置為Deferrable(僅僅是建立非唯一性索引),由于在維護(hù)索引是,假設(shè)違反了唯一性也會報錯,所以必須建立非唯一性索引.比如:
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,將不建立對應(yīng)的索引.
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,將刪除對應(yīng)的索引(除非指定了keep index,可是keep下來的索引是唯一性的,insert數(shù)據(jù)時還是要作唯一性檢查的),在enable時重建索引.
Disbale? deferrable 的pk、uk將保留原來的索引(由于原來的索引就是非唯一性的,不影響insert的操作).
?
3)????? 一些操作經(jīng)驗
KEEP INDEX要注意的:
? a.ALTER TABLE games? DISAble? CONSTRAINT fk_num keep index;--唯一索引被保留,所以還是不能插入反復(fù)的數(shù)據(jù).不應(yīng)該keep index.
? b.ALTER TABLE games? DISAble? CONSTRAINT fk_num;--假設(shè)上一步被運行,那么此語句什么都不做,唯一索引仍被保留,此時應(yīng)該先enable在disable.假設(shè)原來的狀態(tài)是able的話,那么唯一索引將被刪除.
?
?
5.??? 報告約束異常
?
??? 假設(shè)校驗約束時存在異常,則返回一個錯誤,且完整性約束仍保持未被校驗狀態(tài)。當(dāng)約束存在異常時,一個語句就不能正確運行,則此語句被回滾。假設(shè)存在異常,必需要更新或刪除了約束的全部異常后,才干夠校驗約束。可是在使用ALTER TABLE語句不能確定哪一行違反約束,為了確定哪一行,在公布ENABLE子句中帶有EXCEPTION選項的ALTER TABLE語句。
?
??? EXCEPTION選項將ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一個指定的表中。在啟用約束前,硬創(chuàng)建一個合適的異常報告表,用來接收ENABLE子句的EXCEPTION選項信息,能夠直接運行'?\rdbms\admin\utlexcpt.sql'或'?\rdbms\admin\utlexcpt1.sql'腳本來進(jìn)行創(chuàng)建。注意:這兩個腳本的差別在于數(shù)據(jù)庫的兼容性級別和所分析的表的類型。
?
??? 使用的語法例如以下:
?
??? ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
?或者
??? alter table team disable validate?? constraint id_num?? EXCEPTIONS INTO EXCEPTIONS;--這種方法不創(chuàng)建索引
??
??? 假設(shè)存在異常,則直接查詢 SELECT * FROM EXCEPTIONS; 就可以。假設(shè)須要更加具體的信息,則能夠使用ROWID與原表的ROWID進(jìn)行關(guān)聯(lián),這樣就能夠查出原始表中當(dāng)前行的全部信息,并進(jìn)行改動。
怎樣利用EXCEPTIONS來處理實踐中的一些問題:
(1)在已有的constraint上載入數(shù)據(jù)
??????? 先 disable
??????? 再載入
??????? 再enable and EXCEPTIONS INTO EXCEPTIONS
???? 再依據(jù)EXCEPTIONS表的紀(jì)錄delete
????? 最后enable
(2)新建constraint,原有的數(shù)據(jù)違反了這個約束
? ??????先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.???? 關(guān)于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);
?
?
?
改動或刪除現(xiàn)有的約束
?
??? 注:在使用外鍵參考了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;
?
??? 若要停用/刪除相關(guān)的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;
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
