Deferred Constraint Check-优快云博客

本文链接：https://blog.youkuaiyun.com/chncaesar/article/details/17319873

本文介绍在SQL中如何通过创建可延迟的约束来控制外键检查的时间点，包括两种方式：一是通过会话设置使所有约束变为延迟检查；二是直接定义约束为初始即延迟检查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Two ways to accomplish such goal:

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

Constraints are created initially deferred deferrable.

alter session set constraints = immediate;
alter table c drop constraint pid_fk;
TRUNCATE TABLE P;
TRUNCATE TABLE C;
alter table c add constraint pid_fk foreign key (p_id) references p(p_id) INITIALLY DEFERRED deferrable;

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.