oracle延迟,ORACLE 约束延迟验证的特性deferred的作用与场景？

SQL> create table p ( pk int primary key );

Table created.

SQL>  create table c( t1 int,fk int constraint c_fk references p(pk) deferrable initially immediate);

Table created.

SQL> insert into p values ( 1 );

1 row created.

SQL> insert into p values ( 3 );

1 row created.

SQL> insert into c values ( 2,1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p;

PK

----------

1

3

SQL> select * from c;

T1         FK

---------- ----------

2          1

此时若你想将  P 和 C表中的PK=1 FK=1  都更新为PK=2 FK=2 如何做呢？

SQL> update p set pk=2 where pk=1;

update p set pk=2 where pk=1

*

ERROR at line 1:

ORA-02292: integrity constraint (SYS.C_FK) violated - child record found

SQL> update c set fk=2 where fk=1;

update c set fk=2 where fk=1

*

ERROR at line 1:

ORA-02291: integrity constraint (SYS.C_FK) violated - parent key not found

你单独更新 P 和 C 表都不可以， 要么你删除数据后 重新插入

要么：

set constraint c_fk deferred;

SQL> set constraint c_fk deferred;

Constraint set.

SQL> update p set pk=2 where pk=1;

1 row updated.

SQL> update c set fk=2 where fk=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from p;

PK

----------

2

3

SQL> select * from c;

T1         FK

---------- ----------

2          2

这样够清楚了吗？