Deferred Constraint Check

最新推荐文章于 2023-02-18 09:52:09 发布

原创最新推荐文章于 2023-02-18 09:52:09 发布 · 854 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Deferred #Constraint

Oracle 专栏收录该内容

33 篇文章

订阅专栏

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

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.