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/