Deferring Constraint Checking: check constraint when 'commit' : Defer Constraint « Constraints « Oracle PL / SQL

1. Oracle PL / SQL
2. Constraints
3. Defer Constraint

Deferring Constraint Checking: check constraint when 'commit'

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>
           
       

Related examples in the same category