Deferred Constraint Check

Two ways to accomplish such goal:

1. Constraints are created deferrable.

     Alter session set constraints = deferred.

create table p( 

p_id number primary key);

create table c(

c_id number primary key,

p_id number

);

alter table c add constraint pid_fk foreign key (p_id) references p(p_id) deferrable;

table C altered.

alter session SET CONSTRAINTS = DEFERRED;

session SET altered.

begin

  insert into c values(1,1);

  insert into p values(1);

  commit;

end;

anonymous block completed.

SELECT * FROM C;

C_ID    P_ID

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

       1            1

2. Constraints are created initially deferred deferrable.

alter table c drop constraint pid_fk;

table C altered.

TRUNCATE TABLE P;

TRUNCATE TABLE C;

table P truncated.

table C truncated.

alter table c add constraint pid_fk foreign key (p_id) references p(p_id) INITIALLY DEFERRED deferrable;

table C altered.

In the other session:

BEGIN

INSERT INTO C VALUES(1,1);

INSERT INTO P VALUES(1);

COMMIT;

END;

anonymous block completed

PS: alter session set constraints = immediate makes initially deferred deferrable constraint checks violation immediately after each statement. Hence, the constraint is not deferred any more.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/638844/viewspace-774004/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/638844/viewspace-774004/