Oracle約束的啟用和停用
?
??? 關(guān)于Oracle的約束概念和基本操作，我已經(jīng)在以前的《Constraint基礎(chǔ)概念》、《Constraint的簡(jiǎn)單操作》兩篇文章中有過(guò)比較詳細(xì)的介紹了，但是對(duì)于如何停用和啟用constraint沒(méi)有作特別的描述，以至于在使用PLSQL中無(wú)法忽略constraint而逐步進(jìn)行數(shù)據(jù)的更改，所以在這里專門(mén)記錄一下關(guān)于constraint的停用和啟用相關(guān)知識(shí)。
?
一、約束的狀態(tài)
?
??? 可以指定啟用(ENABLE)或者停用(DISABLE)約束。如果啟用約束，當(dāng)在數(shù)據(jù)庫(kù)中輸入或者更新數(shù)據(jù)時(shí)要進(jìn)行數(shù)據(jù)檢查，不符合約束規(guī)則的數(shù)據(jù)不能輸入數(shù)據(jù)庫(kù)。如果停用約束，那么不符合規(guī)則的數(shù)據(jù)也能輸入到數(shù)據(jù)庫(kù)中。
另外，也能制定表中的現(xiàn)有數(shù)據(jù)必須符合約束(VALIDATE)；相反地，如果指定為(NOVALIDATE)，就不能確保現(xiàn)有的數(shù)據(jù)符合約束。
?
??? 所以由上述的特性可知，表上的約束可能處在以下4種狀態(tài)：
??? ● ENABLE , VALIDATE
??? ● ENABLE , NOVALIDATE
??? ● DISABLE, VALIDATE
??? ● DISABLE, NOVALIDATE
?
1、停用約束的情況
?
??? 一般來(lái)說(shuō)，約束都是需要始終處于啟用狀態(tài)的，但是在以下情況中可能需要約束停用：
?
??? ● 當(dāng)往表中裝載大量數(shù)據(jù)時(shí)
??? ● 當(dāng)執(zhí)行批操作使得表有大規(guī)模的改變時(shí)
??? ● 到一次性導(dǎo)入或?qū)С鲆粋€(gè)表時(shí)
?
??? 停用都是為了提高性能，特別是在數(shù)據(jù)倉(cāng)庫(kù)中，這種操作的作用尤其明顯。而且由于停用約束有可能造成違反約束的數(shù)據(jù)輸入到表中，所以在進(jìn)行完以上的動(dòng)作之后，需要立即啟用約束。
?
2、啟用約束的一些問(wèn)題
?
??? 在停用約束時(shí)，違反約束的行可以插入到表中，這種行為被認(rèn)為是對(duì)約束異常。而且如果約束處于啟用非校驗(yàn)狀態(tài)(ENABLE,NOVALIDATE)，則在停用時(shí)插入的違反約束的記錄仍然保留，需要更改后，約束方可出在已校驗(yàn)狀態(tài)。
?
??? 當(dāng)表的約束處于啟用非校驗(yàn)狀態(tài)時(shí)，表中原本的數(shù)據(jù)可以不符合約束，但后面新增的數(shù)據(jù)必須進(jìn)行校驗(yàn)，這對(duì)于數(shù)據(jù)倉(cāng)庫(kù)來(lái)說(shuō)特別有用。因?yàn)榛旧蠑?shù)據(jù)倉(cāng)庫(kù)都是使用增量更新，在停用約束后如果采用啟用校驗(yàn)約束狀態(tài)，則需要對(duì)大量數(shù)據(jù)進(jìn)行校驗(yàn)，影響性能。
?
??? 另外說(shuō)明：校驗(yàn)一個(gè)已經(jīng)啟用的約束，在校驗(yàn)期間不需要任何DML鎖，因此在校驗(yàn)期間可以保證沒(méi)有引入違反約束的數(shù)據(jù)。
?
3、完整性約束狀態(tài)：過(guò)程和效益
?
??? 一般可以按照以下的順序來(lái)使用約束，以確保最佳的效益：
?
??? ① 停用狀態(tài)
??? ② 執(zhí)行操作(裝載、導(dǎo)出、導(dǎo)入)
??? ③ 啟用非校驗(yàn)狀態(tài)
??? ④ 啟用狀態(tài)
?
??? 好處是：
?
??? ● 不留鎖
??? ● 所有的約束能夠同時(shí)處于啟用狀態(tài)
??? ● 約束啟用是并行實(shí)現(xiàn)的
??? ● 允許表上進(jìn)行同步操作
?
?
二、在定義中設(shè)置約束屬性
?
??? 在CREATE TABLE和ALTER TABLE語(yǔ)句中就可以設(shè)置約束的ENABLE/DISABLE、VALIDATE/NOVALIDATE。
??? 注：默認(rèn)的是ENABLE/VALIDATE的。
?
1、在定義中停用約束
?
??? CREATE TABLE emp( empno NUMBER(5) PRIMARY KEY DISABLE,
?????????????????? ?? deptno NUMBER(2));
?
??? ALTER TABLE emp ADD PRIMARY KEY(empno) DISABLE;
?
2、在定義中啟用約束
?
??? CREATE TABLE emp( empno NUMBER(5) PRIMARY KEY,
?????????????????? ?? deptno NUMBER(2));
?
??? ALTER TABLE emp ADD PRIMARY KEY(empno);
???
? ? 注：有可能創(chuàng)建約束失敗，因?yàn)樵紨?shù)據(jù)與約束沖突，此時(shí)語(yǔ)句回滾。
?
3、修改或刪除現(xiàn)有的約束
?
??? 注：在使用外鍵參考了PRIMARY或UNIQUE鍵時(shí)，不能停用或刪除被參考約束
?
??? 停用：
??? ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
??? ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,
???????????????????? DISABLE UNIQUE (dname, loc) KEEP INDEX;
?
??? 啟用非校驗(yàn)：
??? ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
??? ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,
???????????????????? ENABLE NOVALIDATE UNIQUE (dname, loc);
?
??? 啟用校驗(yàn)：
??? 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í)約束檢查
?
??? Oracle可以再事務(wù)中先不檢查約束，直到事務(wù)結(jié)束時(shí)才統(tǒng)一檢查。這樣就可以讓事務(wù)在處理的過(guò)程中暫時(shí)得違反約束，例如某時(shí)候在往表里添加數(shù)據(jù)時(shí)，某字段暫時(shí)留空，在下一步中再update數(shù)據(jù)，以逐步插入數(shù)據(jù)，這樣就必須用到這個(gè)性能。使用SET CONSTRAINTS語(yǔ)句進(jìn)行設(shè)置。
?
??? 在使用這個(gè)特性時(shí)，需要注意幾點(diǎn)：
???
??? ● 不能在觸發(fā)器內(nèi)發(fā)布SET CONSTRAINTS語(yǔ)句
??? ● 可延時(shí)的唯一鍵和主鍵必須使用非唯一索引
?
1、所有約束設(shè)置成延時(shí)
?
??? SET CONSTRAINTS ALL DEFERRED;
?
??? 注：在所有處理前執(zhí)行該語(yǔ)句，至事務(wù)結(jié)束時(shí)為止，只適用于當(dāng)前會(huì)話。
?
2、檢查提交(可選)
?
??? 在事務(wù)COMMIT之前，可以用 SET CONSTRAINTS ALL IMMEDIATE 語(yǔ)句來(lái)手工檢查約束的違反情況。雖然在事務(wù)結(jié)束時(shí)會(huì)自動(dòng)隱性執(zhí)行這條語(yǔ)句，但是也是有一定的存在意義的，例如希望在錯(cuò)誤時(shí)定義某些操作。
?
?
四、報(bào)告約束異常
?
??? 如果校驗(yàn)約束時(shí)存在異常，則返回一個(gè)錯(cuò)誤，且完整性約束仍保持未被校驗(yàn)狀態(tài)。當(dāng)約束存在異常時(shí)，一個(gè)語(yǔ)句就不能正確執(zhí)行，則此語(yǔ)句被回滾。如果存在異常，必須要更新或刪除了約束的所有異常后，才可以校驗(yàn)約束。但是在使用ALTER TABLE語(yǔ)句不能確定哪一行違反約束，為了確定哪一行，在發(fā)布ENABLE子句中帶有EXCEPTION選項(xiàng)的ALTER TABLE語(yǔ)句。
?
??? EXCEPTION選項(xiàng)將ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一個(gè)指定的表中。在啟用約束前，硬創(chuàng)建一個(gè)合適的異常報(bào)告表，用來(lái)接收ENABLE子句的EXCEPTION選項(xiàng)信息，可以直接執(zhí)行'D:\oracle\ora92\rdbms\admin\utlexcpt.sql'或'D:\oracle\ora92\rdbms\admin\utlexcpt1.sql'腳本來(lái)進(jìn)行創(chuàng)建。注意：這兩個(gè)腳本的區(qū)別在于數(shù)據(jù)庫(kù)的兼容性級(jí)別和所分析的表的類型。
?
??? 使用的語(yǔ)法如下：
?
??? ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
?
??? 如果存在異常，則直接查詢 SELECT * FROM EXCEPTIONS; 即可。如果需要更加詳細(xì)的信息，則可以使用ROWID與原表的ROWID進(jìn)行關(guān)聯(lián)，這樣就可以查出原始表中當(dāng)前行的所有信息，并進(jìn)行修改。
?
?
?