Deferred Constraints : Constraint « Table « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Table
  3. Constraint
  1. A deferred constraint is one that is enforced when a transaction is committed.
  2. A deferrable constraint is specified by using DEFERRABLE clause.
  3. Once you've added a constraint, you cannot change it to DEFERRABLE. You must drop and recreate the constraint.
  4. When you add a DEFERRABLE constraint, you can mark it as INITIALLY IMMEDIATE or INITIALLY DEFERRED.
  5. INITIALLY IMMEDIATE means that the constraint is checked whenever you add, update, or delete rows from a table.
  6. INITIALLY DEFERRED means that the constraint is only checked when a transaction is committed.
SQL> -- create demo table
SQL> create table myTable(
  2    id           NUMBER(2),
  3    value        NUMBER(6,2)
  4  )
  5  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into myTable(ID,  value)values (1,9)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (2,2.11)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (3,3.44)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (4,-4.21)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (5,10)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (6,3)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (7,-5.88)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (8,123.45)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (9,98.23)
  2  /

1 row created.

SQL>
SQL> select * from myTable
  2  /

        ID      VALUE
---------- ----------
         1          9
         2       2.11
         3       3.44
         4      -4.21
         5         10
         6          3
         7      -5.88
         8     123.45
         9      98.23

9 rows selected.

SQL>
SQL>
SQL>
SQL> ALTER TABLE myTable
  2  ADD CONSTRAINT uq UNIQUE (id)
  3  DEFERRABLE INITIALLY DEFERRED;

Table altered.

SQL>
SQL>
SQL> -- clean the table
SQL> drop table myTable
  2  /

Table dropped.

SQL>
SQL>








6.10.Constraint
6.10.1.Adding a Constraint
6.10.2.Add constaint for a date type column: larger than a certain date
6.10.3.Adding a 'NOT NULL' Constraint
6.10.4.The database automatically assigns a name to the constraint
6.10.5.Dropping a Constraint
6.10.6.Disabling a Constraint
6.10.7.Add CASCADE to the end of a DISABLE CONSTRAINT clause
6.10.8.Enabling a Constraint
6.10.9.You can also choose to apply a constraint to new data only by specifying ENABLE NOVALIDATE.
6.10.10.Deferred Constraints
6.10.11.How to drop a constaint
6.10.12.Violate a constraint
6.10.13.Cascade constraints