使用deferred constraint 解决“先有蛋还是先有鸡的问题”
SQL> create table chicken( cid number primary key,
2 eid number references egg(id));
eid number references egg(id))
*
ERROR at line 2:
ORA-00942: 表或视图不存在
SQL> create table egg (eid number primary key,
2 cid number references chicken(cid));
cid number references chicken(cid))
*
ERROR at line 2:
ORA-00942: 表或视图不存在
通过上述的语句显然不能创建chicken 表和egg 表，因为他们之间的依存关系。
我们可以通过先创建表，在通过alter table 创建约束。
SQL> create table chicken(cid number primary key,
2 eid number);
Table created.
SQL> create table egg(eid number primary key,
2 cid number);
Table created.
SQL> alter table chicken add constraint chicken_ref_egg
2 foreign key (eid) references egg(eid);
Table altered.
SQL> alter table egg add constraint egg_ref_chicken
2 foreign key (cid) references chicken(cid);
Table altered.
到现在为止，好像一切都是正常的，我们试着插入记录：
id 为1 的鸡，生了2个蛋。这2个蛋是由id 为1 的鸡生的。这段话好纠结。。。
SQL> insert into chicken values(1,2);
insert into chicken values(1,2)
*
ERROR at line 1:
ORA-02291: 违反完整约束条件 (HR.CHICKEN_REF_EGG) - 未找到父项关键字
SQL> insert into egg values(2,1);
insert into egg values(2,1)
*
ERROR at line 1:
ORA-02291: 违反完整约束条件 (HR.EGG_REF_CHICKEN) - 未找到父项关键字
还是因为chicken 表和egg 表的依赖关系的原因导致插入不成功。因为往chicken
表中插入记录的时候在父表egg 中找不到相应的记录，往egg 中插入记录也是一样的。
SQL> alter table chicken drop constraint chicken_ref_egg;
Table altered.
SQL> alter table egg drop constraint egg_ref_chicken;
Table altered.
SQL> edit
Wrote file afiedt.buf
1 alter table chicken add constraint
2 chicken_ref_egg
3 foreign key(eid) references egg(eid)
4* initially deferred deferrable
SQL> /
Table altered.
SQL> edit
Wrote file afiedt.buf
1 alter table egg add constraint
2 egg_ref_chicken
3 foreign key(cid) references chicken(cid)
4* initially deferred deferrable
SQL> /
Table altered.
使用deferred constraint 把对约束的检查推迟到事务commit 的时候。
并且同时往chicken 和 egg 表中都插入数据，构成一个事务，最后提交
现在"先有蛋还是先有鸡的问题"就解决了。而且改变下面两条insert 语句的
执行顺序结果也是一样的。充分体现了事务的灵活性。
SQL> insert into chicken values(1,2);
1 row created.
SQL> insert into egg values(2,1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from chicken;
CID EID
---------- ----------
1 2
SQL> select * from egg;
EID CID
---------- ----------
2 1
小结：deferred constraint 可以提供更加灵活的事务控制。在有些情况下是非常有用的。