oracle 延迟性约束
--创建一个样例表HR@prod> create table dept2 as select * from departments;
--查看dept2的表拥有哪些约束
HR@prod> select uc.constraint_name,uc.constraint_type,ucc.column_name
2from user_constraints uc,user_cons_columns ucc
3where uc.constraint_name=ucc.constraint_name
4and uc.table_name='DEPT2';
CONSTRAINT_NAME C COLUMN_NAME
------------------------------ - --------------------
SYS_C0011291 C DEPARTMENT_NAME
--给dept2表增加一个延迟性的主键约束
HR@prod> alter table dept2
2add constraint dept2_pk
3primary key(department_id)
4deferrable initially deferred;
Table altered.
--延迟性约束，校验约束规则的时机
HR@prod> insert into dept2 values(280,'MARKET',null,1700);
1 row created.
HR@prod> insert into dept2 values(280,'SALES',null,1800);
1 row created.
HR@prod> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HR.DEPT2_PK) violated
--延迟性约束的延迟状态变为immediate
HR@prod> set constraint dept2_pk immediate;
HR@prod> insert into dept2 values(280,'MARKET',null,1700);
1 row created.
HR@prod> insert into dept2 values(280,'SALES',null,1800);
insert into dept2 values(280,'SALES',null,1800)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEPT2_PK) violated
HR@prod> commit;
--immediate状态，会导致出错语句做语句级回滚，不会影响正常的数据插入；
*************************
--如果想让当前会话，所有的延迟性约束都在当前会话中改变状态：
HR@prod> select constraint_name,deferrable,deferred from user_constraints where constraint_name='DEPT2_PK'
HR@prod> /
CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------ -------------- ---------
DEPT2_PK DEFERRABLE DEFERRED
HR@prod> alter session set constraints=immediate;
HR@prod> insert into dept2 values(290,'SALES',NULL,1700);
1 row created.
HR@prod> insert into dept2 values(290,'haha',null,1700);
insert into dept2 values(290,'haha',null,1700)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEPT2_PK) violated
页:
[1]