Mega Code Archive
Deferring Constraint Checking
<![CDATA[
SQL>
SQL> -- Deferring Constraint Checking: check constraint when 'commit'
SQL>
SQL> CREATE TABLE chicken(chicken_ID INT PRIMARY KEY, egg_ID INT);
Table created.
SQL>
SQL> CREATE TABLE egg(egg_ID INT PRIMARY KEY, chicken_ID INT);
Table created.
SQL>
SQL>
SQL> ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
2 FOREIGN KEY (egg_ID) REFERENCES egg(egg_ID)
3 INITIALLY DEFERRED DEFERRABLE;
Table altered.
SQL>
SQL> ALTER TABLE egg ADD CONSTRAINT eggREFchicken
2 FOREIGN KEY (chicken_ID) REFERENCES chicken(chicken_ID)
3 INITIALLY DEFERRED DEFERRABLE;
Table altered.
SQL>
SQL>
SQL> INSERT INTO chicken VALUES(1, 2);
1 row created.
SQL> INSERT INTO egg VALUES(2, 1);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> select * from chicken;
CHICKEN_ID EGG_ID
---------- ----------
1 2
SQL> select * from egg;
EGG_ID CHICKEN_ID
---------- ----------
2 1
SQL>
SQL> drop table chicken cascade constraint;
Table dropped.
SQL> drop table egg cascade constraint;
Table dropped.
SQL>
SQL>
]]>