1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.

How do I avoid chicken and egg problems?

Discussion in 'SQL - Questions and Answers' started by irfanhab, May 26, 2006.

  1. irfanhab New Member

    Dialect: Oracle
    Say If I have a table which defines a foreign key constraint on another table which itself does not yet exist? I cant create such a table as I keep getting an exception
  2. irfanhab New Member

    Dialect: Oracle
    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.

Share This Page