Deferrable initially immediate : Defer Constraint « Constraints « Oracle PL / SQL

1. Oracle PL / SQL
2. Constraints
3. 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 (JAVA2S.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 (JAVA2S.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 (JAVA2S.X_GREATER_THAN_ZERO) violated


SQL>
SQL>
           
       

Related examples in the same category