ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Deferred Constraint Check

Deferred Constraint Check

原创 Linux操作系统 作者:chncaesar 时间:2013-10-09 16:47:47 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DDL Parameters
请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    899753