Oracle Constraint
Validate确保已有数据符合约束;
Novalidate不必考虑已有数据是否符合约束。
除非Novalidate被指定,Enable默认Validate;
除非Validate被指定,Disable默认Novalidate;
Validate和Novalidate对Enable和Disable没有任何默认暗示。
Enable Validate与Enable相同,检查已有记录和新增记录,确保都符合约束;
Enable Novalidate 允许已有记录不必满足约束条件,但新增/修改的记录必须满足;
Disable Validate禁用约束,删除约束上的索引,不允许修改任何被约束的记录;
Disable Novalidate与Disable相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-484142/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/308563/viewspace-484142/