Oracle PL/SQL/Constraints/Defer Constraint
Deferrable initially immediate
<source lang="sql">
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>
</source>
Deferring Constraint Checking: check constraint when "commit"
<source lang="sql">
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>
</source>