Oracle PL/SQL/Constraints/Defer Constraint

Deferrable initially immediate

SQL>  create table t( x int,
  2                   constraint x_greater_than_zero check ( x > 0 )
  3                   deferrable initially immediate
  4   )
  5   /

SQL>  insert into t values ( -1 );
 insert into t values ( -1 )
*
ERROR at line 1:
ORA-02290: check constraint (sqle.X_GREATER_THAN_ZERO) violated

SQL>
SQL>  select * from t;
no rows selected
SQL>
SQL>
SQL>  set constraint x_greater_than_zero deferred;
Constraint set.
SQL>
SQL>  insert into t values ( -1 );
1 row created.
SQL>
SQL>
SQL>  select * from t;
         X
----------
        -1
SQL>
SQL>  set constraint x_greater_than_zero deferred;
Constraint set.
SQL>
SQL>  insert into t values ( -1 );
1 row created.
SQL>
SQL>  select * from t;
         X
----------
        -1
        -1
SQL>
SQL>  set constraint x_greater_than_zero immediate;
 set constraint x_greater_than_zero immediate
*
ERROR at line 1:
ORA-02290: check constraint (sqle.X_GREATER_THAN_ZERO) violated

SQL>
SQL>  drop table t;
 drop table t
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (sqle.X_GREATER_THAN_ZERO) violated

SQL>
SQL>

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>