_____________________________________________________________

______________________________________________________________

Deferred Integrity Constraints

选项一：DEFERRABLE INITIALLY IMMEDIATE：

SQL> ALTER TABLE  A1

  2   ADD CONSTRAINT A1_C01

  3   CHECK (DT>2)

  4      DEFERRABLE INITIALLY IMMEDIATE;

Table altered.

SQL> SELECT * FROM A1;

CH            DT

----- ----------

4              3

5              8

SQL> Insert into  A1

  2     (CH, DT)

  3   Values

  4     ('12', 1);

Insert into  A1

*

ERROR at line 1:

ORA-02290: check constraint ( A1_C01) violated

SQL> SET CONSTRAINTS ALL DEFERRED;

Constraint set.

SQL> Insert into  A1

  2     (CH, DT)

  3   Values

  4     ('12', 1);

1 row created.

SQL> COMMIT;

COMMIT

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint ( A1_C01) violated

选项二：DEFERRABLE INITIALLY DEFERRED：

ALTER TABLE A1 DROP CONSTRAINT A1_C01

SQL> ALTER TABLE  A1

  2   ADD CONSTRAINT A1_C01

  3   CHECK (DT>=2)

  4      DEFERRABLE INITIALLY DEFERRED;

Table altered.

SQL> Insert into  A1

  2     (CH, DT)

  3   Values

  4     ('12', 1);

1 row created.

SQL> COMMIT; ---提交時才報錯。

COMMIT

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint ( A1_C01) violated