Deferred Constraint Check

chncaesar發表於2013-10-09
AI
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章