Language: PL/SQL Highlight Mode: PLSQL Last Modified: February 27th, 2009
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:CREATETABLE chicken (cID INT PRIMARY KEY,
eID INT REFERENCES egg(eID));CREATETABLE 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 FIRSTINSERT would always be a CONSTRAINT
violation.)
Finally,TO get rid OF the tables, we have TODROP the
constraints FIRST, because Oracle won't allow us to drop a
table that's referenced BY another TABLE.ALTERTABLE egg DROPCONSTRAINT eggREFchicken;ALTERTABLE chicken DROPCONSTRAINT chickenREFegg;DROPTABLE egg;DROPTABLE chicken;
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.