Oracle數據庫Constraint約束的常用操作及異常處理
繼上次我們介紹了：Oracle數據庫中Constraint約束的四對屬性，本次我們就介紹一下Oracle數據庫Constraint約束的常用操作及異常處理，接下來就讓我們來一起了解一下這部分內容。
1.報告約束異常
如果校驗約束時存在異常，則返回一個錯誤，且完整性約束仍保持未被校驗狀態。當約束存在異常時，一個語句就不能正確執行，則此語句被回滾。如果存在異常，必須要更新或刪除了約束的所有異常后，才可以校驗約束。但是在使用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;
2.關于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;
關于Oracle數據庫Constraint約束的一些基本操作就介紹到這里了，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
