建外键的时候加上deferrable;
create table a (col number(5,0),
time date);
create table b (col number(5,0);
insert into a values(2,sysdate);
alter table b add constraint b_FK_a foreign key(col) references a(col) on delete cascade deferrable initially immediate;
insert into b values(2);
这时如果
SQL> update a set col=3 where col=2;
update a set col=3 where col=2
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.B_FK_A) violated - child record found
你可以这样设置
set constraint b_fk_a deferred;
Constraint set.
SQL> update a set col=3 where col=2;
1 row updated.
SQL> update b set col=3 where col=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from a;
COL TIME
---------- ---------
3 10-AUG-04
SQL> select * from b;
COL
----------
3
这样就可以update了。
在你commit后,这个constraint会自动恢复成immediate模式,你也可以用
set constraint b_fk_a immediate手动恢复