130821延迟约束

最新推荐文章于 2024-01-02 17:36:32 发布

calvin82

最新推荐文章于 2024-01-02 17:36:32 发布

阅读量304

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/calvin82/article/details/10137849

版权

SQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

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