130821延迟约束

来源：互联网发布：实测实量数据上墙编辑：程序博客网时间：2024/05/16 03:46

SQL> alter table calvin.test2 add constraint test2_name_uni unique(name) deferrable initially deferred;

Table altered

延迟约束，只能在初始创建该约束时候添加，如果是 deferrable initially immediate 则增删改数据会立即检查；如果是deferrable initially deferred只有在提交时检查；

该延迟只能在创建约束的时候使用，否则只能删除约束重建；

SQL> desc calvin.test2;
Name Type Nullable Default Comments
----- -------------- -------- ------- --------
ID INTEGER
NAME VARCHAR2(2000) Y
TEST2 CHAR(1) Y 1

SQL> select * from calvin.test2;

ID NAME TEST2
--------------------------------------- -------------------------------------------------------------------------------- -----
1 t 1

SQL> insert into calvin.test2 values(2,'t',1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-00001: unique constraint (CALVIN.TEST2_NAME_UNI) violated

SQL> select * from calvin.test2;

ID NAME TEST2
--------------------------------------- -------------------------------------------------------------------------------- -----
1 t 1

SQL> insert into calvin.test2 values(2,'t',1);

1 row inserted

SQL> select * from calvin.test2;

ID NAME TEST2
--------------------------------------- -------------------------------------------------------------------------------- -----
2 t 1
1 t 1

SQL> update calvin.test2 set name = 't1' where id =2;

1 row updated

SQL> select * from calvin.test2;

ID NAME TEST2
--------------------------------------- -------------------------------------------------------------------------------- -----
2 t1 1
1 t 1

SQL> commit;

Commit complete

SQL> select owner,constraint_name, constraint_type status, deferrable, deferred, validated from user_constraints uc where uc.table_name = 'TEST2';

OWNER CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------------------------------------ ------------------------------ ------ -------------- --------- -------------
CALVIN TEST1_ID_PK P NOT DEFERRABLE IMMEDIATE VALIDATED
CALVIN TEST2_NAME_UNI U DEFERRABLE DEFERRED VALIDATED
CALVIN TEST2_ID_CK C NOT DEFERRABLE IMMEDIATE VALIDATED