使用deferred constraint 解決“先有蛋還是先有雞的問題”

pingley發表於2012-05-13
AI
使用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 可以提供更加靈活的事務控制。在有些情況下是非常有用的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26110315/viewspace-723467/，如需轉載，請註明出處，否則將追究法律責任。

相關文章