Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Deferring Constraint Checking Jump to:  
Category: >> Oracle PL/SQL >> Deferring Constraint Checking Bookmark and Share

<< lastnext >>

Snippet Name: Deferring Constraint Checking

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

Also see:
» Check Constraint - Create
» Unique Constraint
» Primary Key Constraint
» Foreign Key Constraints
» Current_timestamp
» Add Primary Key Constraint example
» Constraint Checks
» Add constraint example

Comment: (none)

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:
 
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;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


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.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 79 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?