Oracle PL/SQL/Constraints/Defer Constraint

Материал из SQL эксперт
Перейти к: навигация, поиск

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>