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;