INITIALLY DEFERRED DEFERRABLE -(Deferring Constraint Checking)

Sometimes it is necessary to defer the checking of certain constraints, most commonly in the "chicken-and-egg" problem. Suppose we want to say:

CREATE TABLE chicken (cID INT PRIMARY KEY,

                      eID INT REFERENCES egg(eID));

CREATE TABLE egg(eID INT PRIMARY KEY,

                 cID INT REFERENCES chicken(cID));

But if we simply type the above statements into Oracle, we'll get an error. The reason is that the CREATE TABLE statement for chicken refers to table egg, which hasn't been created yet! Creating egg won't help either, because egg refers to chicken.

To work around this problem, we need SQL schema modification commands. First, create chicken and egg without foreign key declarations:

CREATE TABLE chicken(cID INT PRIMARY KEY,

                     eID INT);

CREATE TABLE egg(eID INT PRIMARY KEY,

                 cID INT);

Then, we add foreign key constraints:

ALTER TABLE chicken ADD CONSTRAINT chickenREFegg

    FOREIGN KEY (eID) REFERENCES egg(eID)

    INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE egg ADD CONSTRAINT eggREFchicken

    FOREIGN KEY (cID) REFERENCES chicken(cID)

    INITIALLY DEFERRED DEFERRABLE;

INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking.

For example, to insert (1, 2) into chicken and (2, 1) into egg, we use:

INSERT INTO chicken VALUES(1, 2);

INSERT INTO egg VALUES(2, 1);

COMMIT;

Because we've declared the foreign key constraints as "deferred", they are only checked at the commit point. (Without deferred constraint checking, we cannot insert anything into chicken and egg, because the first INSERT would always be a constraint violation.)

Finally, to get rid of the tables, we have to drop the constraints first, because Oracle won't allow us to drop a table that's referenced by another table.

ALTER TABLE egg DROP CONSTRAINT eggREFchicken;

ALTER TABLE chicken DROP CONSTRAINT chickenREFegg;

DROP TABLE egg;

DROP TABLE chicken;