Deferring Constraint Checking

Description: Sometimes it is necessary to defer the checking of certain constraints, most commonly in the “chicken-and-egg” problem.

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 w3mentor, 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;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.