Oracle数据库Constraint约束的常用操作及异常处理

摘自：http://oracle.chinaitlab.com/induction/860917.html

注：约束分为

unique约束
check约束
primary约束
foreign key约束
not null约束等

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约束的一些基本操作就介绍到这里了,希望本次的介绍能够对您有所收获!

摘自：http://www.cnblogs.com/rootq/archive/2008/09/23/1297400.html

disable/enable validate/novalidate 的区别

启用约束:
enable( validate) :启用约束,创建索引,对已有及新加入的数据执行约束.
enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据.

禁用约束:
disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作.
disable validate :关闭约束,删除索引,不能对表进行 插入/更新/删除等操作.


环境:oracle 9i 9.0.1.0 for win,以上结论均测试通过.
例:disable validate约束后,执行update...操作提示:
ORA-25128: 不能对带有禁用和验证约束条件 (SYS.PK_EMP_01) 的表进行插入/更新/删除

=============================================================

今天看到一个关于enable novalidate的问题,以前书上看到过,但没仔细研究,趁此机会分析了一下,把结果发上来供大家参考.
首先对constraint的开启和关闭共有四种:enable validate;enable novalidate;disable validate;disable novalidate;
1,3没有什么好说的,2表示开启时不检查已存在数据,4表示不检查今后的数据.
为此做如下测试:
1.首先创建测试表
create table a (no number(5),name varchar2(10));
insert into a values(1,'ss');
alter table a add constraint pk_a primary key(no);

create table b (no number(5),name varchar2(10));
insert into a values(1,'ss');
ALTER TABLE b ADD CONSTRAINT fk_a_b
FOREIGN KEY (no)
REFERENCES a (no);

2.测试fk
SQL> insert into b values(2,'sd');
insert into b values(2,'sd')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.FK_A_B) violated - parent key not found
为此使用
SQL> alter table b disable novalidate constraint fk_a_b;

Table altered.

insert into b values(2,'sdd')
SQL> /

1 row created.
SQL> alter table b enable novalidate constraint fk_a_b;

Table altered.
从上面测试结果可见enable novalidate和disable novalidate在fk等约束是好用的.
3.测试pk
SQL> alter table a disable novalidate primary key;

Table altered.
SQL> insert into a values(1,'sd');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table a enable novalidate primary key;
alter table a enable novalidate primary key
*
ERROR at line 1:
ORA-02437: cannot validate (SYSTEM.SYS_C001578) - primary key violated
在pk测试中enable novalidate不能使用了.
看到kamus的解释说是enable novalidate主键必须是唯一.
查了下相关资料,没有发现上述说明,但是找到一个下面的说法:
Primary and unique keys must use nonunique indexes
从新做测试
4.测试pk(2)
SQL> alter table a disable novalidate primary key;

Table altered.
这时我去检查表a,发现主键对应的索引pk_a不在了.
手工创建索引
SQL> create index i_a on a(no);

Index created.

SQL> alter table a enable novalidate primary key;

Table altered.
居然成功了.

结论:
从上面的测试结果看出,novalidate在非pk;un时可以正常工作.
在对pk;un使用时需要先创建相关索引,再使用novalidate.