View Single Post
  #2 (permalink)  
Old 05-26-2006, 04:37 AM
irfanhab irfanhab is offline
Junior Member
 
Join Date: May 2006
Posts: 10
irfanhab is on a distinguished road
Default Oracle answer. Re: How do I avoid chicken and egg problems?

You can use deferrable constraint checking in this scenario
Code:
CREATE TABLE chicken(chickenID INT PRIMARY KEY, eggID INT);
CREATE TABLE egg(eggID INT PRIMARY KEY, chickenID INT);

Then, we add foreign key constraints:

ALTER TABLE chicken ADD CONSTRAINT chicken_gg
    FOREIGN KEY (eggID) REFERENCES egg(eggID)
    INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE egg ADD CONSTRAINT egg_chicken
    FOREIGN KEY (chickenID) REFERENCES chicken(chickenID)
    INITIALLY DEFERRED DEFERRABLE;
After creating deferrable constraint checking, Oracle (or any other dbserver) will check constraints only on commit events.
Reply With Quote