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

【编辑推荐】

数据库集群中间件CJDBC详细介绍
SQL Server 2005无法连接到本地服务器的解决
Linux下重新配置MySQL数据库引擎innodb的过程
SQL Server通过整理索引碎片和重建索引提高速度
Linux+Oracle 10g RAC双网卡绑定和解除绑定的实现

网页名称：Oracle数据库Constraint约束的常用操作及异常处理
当前网址：http://www.wtcwzsj.com/article/djdcoeg.html

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

其他资讯

公司服务热线