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
以下是一个新建外键的例子：
1.alter table team add constraint id_num foreign key(id) references games(scores) INITIALLY disable;
2.
3.alter table team enable constraint id_num EXCEPTIONS INTO EXCEPTIONS;
4.
5.select * from EXCEPTIONS;
2.关于contraint的一些常见操作
定义约束：
1.alter table t add constraint ch_100 unique(id) disable ;
2.
3.alter table t add constraint ch_100 unique(id);
4.
5.alter table t add constraint ch_100 unique(id) deferred deferrable novalidate;
7.CREATE TABLE dept_20
8.
9.(employee_id NUMBER(4),
10.
mission_pct NUMBER(7,2),
12.
13.department_id,
14.
15.CONSTRAINT fk_deptno
16.
17.FOREIGN KEY (department_id)
18.
19.REFERENCES departments(department_id)
20.
21.on delete/set null cascade);
22.
23.ALTER TABLE dept_20
24.
25.ADD CONSTRAINT fk_empid_hiredate
26.
27.FOREIGN KEY (employee_id, hire_date)
28.
29.REFERENCES hr.job_history(employee_id, start_date)
30.
31.EXCEPTIONS INTO EXCEPTIONS;
32.
33.CREATE TABLE divisions
34.
35.(div_no NUMBER CONSTRAINT check_divno
36.
37.CHECK (div_no BETWEEN 10 AND 99)
38.
39.DISABLE,
40.
41.div_name VARCHAR2(9) CONSTRAINT check_divname
42.
43.CHECK (div_name = UPPER(div_name))
44.
45.DISABLE,
46.
47.office VARCHAR2(10) CONSTRAINT check_office
48.
49.CHECK (office IN ('DALLAS','BOSTON',
51.'PARIS','TOKYO'))
52.
53.DISABLE);
注：在使用外键参考了PRIMARY或UNIQUE键时，不能停用或删除被参考约束
延迟校验的启用停用：
1.SET CONSTRAINT(s) unq_num/all immediate;
2.
3.SET CONSTRAINT(s) unq_num/all deferred;
停用：
1.ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
2.
3.ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,
4.
5.DISABLE UNIQUE (dname, loc) KEEP INDEX;
启用非校验：
1.ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
2.
3.ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,
4.
5.ENABLE NOVALIDATE UNIQUE (dname, loc);
启用校验：
1.ALTER TABLE dept MODIFY CONSTRAINT dname_ukey VALIDATE;
2.
3.ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;
若要停用/删除相关的FOREIGN KEY约束，则：
ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
删除：
1.ALTER TABLE dept DROP UNIQUE (dname, loc);
2.
3.ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX,
4.
5.DROP CONSTRAINT dept_fkey;
来源：网络编辑：联动北方技术论坛。