EODA@PROD1> set echo on
EODA@PROD1> create table parent ( pk int primary key );
Table created.
EODA@PROD1> create table child
2 ( fk constraint child_fk_parent
3 references parent(pk)
4 deferrable --设置为有可延迟的约束
5 initially immediate
6 )
7 /
Table created.
EODA@PROD1> insert into parent values ( 1 );
1 row created.
EODA@PROD1> insert into child values ( 1 );
1 row created.
EODA@PROD1> update parent set pk = 2; --更新报错由于检查约束失败
update parent set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (EODA.CHILD_FK_PARENT) violated - child record found
EODA@PROD1> set constraint child_fk_parent deferred; --设置为deferred约束模式
Constraint set.
EODA@PROD1> update parent set pk = 2; --更新成功
1 row updated.
EODA@PROD1> set constraint child_fk_parent immediate; --设置回immediate约束模式失败,因为当前状态违反约束
set constraint child_fk_parent immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (EODA.CHILD_FK_PARENT) violated - parent key not found
EODA@PROD1> update child set fk = 2; --修改子表
1 row updated.
EODA@PROD1> set constraint child_fk_parent immediate; --设置成功
Constraint set.
EODA@PROD1> commit;
Commit complete.
DEFERRABLE约束切换实验
最新推荐文章于 2021-04-03 05:42:33 发布