Oracle->> ENABLE VALIDATE & DISABLE VALIDATE

Here's a blog post explaining these two usages: http://www.cnblogs.com/rootq/archive/2008/09/23/1297400.html

To enable constraints:

Enable (Validate): Enables constraints, creates indexes, and enforces constraints on existing and newly added data. E

Enable Novalidate: Enables constraints, creates indexes, enforces constraints on newly added data only, regardless of existing data in the table.  

To disable a constraint:

Disable (Novalidate): Close the constraint, delete the index, you can modify the data of the constraint column and other operations.

Disable Validate: Close constraints, delete indexes, do not insert/update/delete tables.

I did the experiment here, and I did verify the above statement.

Here are the results of the disable validate experiment.

DeclareNum Number; begin       Select Count(1) intoNum fromAll_tableswheretable_name= 'TEST2'; ifNum=1    Then           ExecuteImmediate'drop table TEST2'; End   if; End; /Create TableTest2 (col1int CONSTRAINTCons_test2_1 not NULL CHECK(col1>Ten) DISABLE validate,col2varchar( -)NULL);/Insert  intoTest2 (Col1,col2)Values(1,'a');

Error starting command execution on line 32:
Insert into Test2 (col1,col2) VALUES (1, ' a ')
Command error, line: 32 columns: 1
Error Reporting:
SQL Error: ORA-25128: cannot be on with disabled and validation constraints (SYSTEM. sys_c009875) Table for Insert/update/delete
25128.00000-"No insert/update/delete on table with constraint (%s.%s) disabled and validated"
*cause:try to insert/update/delete in table with DISABLE VALIDATE constraint.
*action:change the constraint ' s states.

Here are the results of the Enanle validate experiment.

DeclareNum Number; begin       Select Count(1) intoNum fromAll_tableswheretable_name= 'TEST2'; ifNum=1    Then           ExecuteImmediate'drop table TEST2'; End   if; End; /Create TableTest2 (col1int CONSTRAINTCons_test2_1 not NULL CHECK(col1>Ten) ENABLE validate,col2varchar( -)NULL);/Insert  intoTest2 (Col1,col2)Values(1,'a');

Error starting command execution on line 32:
Insert into Test2 (col1,col2) VALUES (1, ' a ')
Error Reporting:
SQL Error: ORA-02290: violation of CHECK constraint (SYSTEM. sys_c009877)
02290.00000-"Check constraint (%s.%s) violated"
*cause:the values being inserted do not satisfy the named check

*action:do not inserts values that violate the constraint.

The following is an experimental result of enable novalidate.

DeclareNum Number; begin       Select Count(1) intoNum fromAll_tableswheretable_name= 'TEST2'; ifNum=1    Then           ExecuteImmediate'drop table TEST2'; End   if; End; /Create TableTest2 (col1int, col2varchar( -)NULL);/Insert  intoTest2 (Col1,col2)Values(1,'a');/Alter TableTest2Add CONSTRAINTCons_test2_1CHECK(col1>Ten) ENABLE novalidate;/Select *  fromTest2;/Insert  intoTest2 (Col1,col2)Values(2,'b');

Table TEST2 has been created. 1 rows are inserted. Table TEST2 has been changed. >>query Run in: Query Results 1

Error starting command execution on line 41: INSERT INTO TEST2 (col1,col2) VALUES (2, ' B ') Error report: SQL error: ORA-02290: violation of CHECK constraint (SYSTEM. Cons_test2_1) 02290.            00000-"Check constraint (%s.%s) violated" *cause:the values being inserted do not satisfy the named check *action:do not inserts values that violate the constraint.

Oracle->> ENABLE VALIDATE & DISABLE VALIDATE