Enable constraints:
Enable (validate): enable constraints, create indexes, and execute constraints on existing and new data.
Enable novalidate: enable constraints, create indexes, and only enforce constraints on new data, regardless of existing data in the table.
Forbidden constraint:
Disable (novalidate): disables the constraint and deletes the index. You can modify the data of the constraint column.
Disable validate: disables constraints and deletes indexes. You cannot insert, update, or delete a table.
Environment: oracle 9i 9.0.1.0 for win, all of the above conclusions have passed the test.
For example, after the disable validate constraint is executed, the update... Operation prompt is displayed:
ORA-25128: cannot insert/update/delete a table with a disabled and validation constraint (SYS. PK_EMP_01)
========================================================== ==================================
Today, I saw a question about enable novalidate. I have seen it in the previous book, but I did not study it carefully. I took this opportunity to analyze it and send the result for your reference.
Enable and disable constraint: enable validate; enable novalidate; disable validate; disable novalidate;
1. There is nothing to say. 2 indicates that existing data is not checked when enabling, and 4 indicates that future data is not checked.
Perform the following tests:
1. First create a test table
Create table a (no number (5), name varchar2 (10 ));
Insert into a values (1, 'ss ');
Alter table a add constraint pk_a primary key (no );
Create table B (no number (5), name varchar2 (10 ));
Insert into a values (1, 'ss ');
Alter table B add constraint fk_a_ B
Foreign key (no)
REFERENCES a (no );
2. Test fk
SQL> insert into B values (2, 'sd ');
Insert into B values (2, 'sd ')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM. FK_A_ B) violated-parent key not found
Use
SQL> alter table B disable novalidate constraint fk_a_ B;
Table altered.
Insert into B values (2, 'sdd ')
SQL>/
1 row created.
SQL> alter table B enable novalidate constraint fk_a_ B;
Table altered.
From the test results above, we can see that the constraints such as enable novalidate and disable novalidate are useful.
3. Test pk
SQL> alter table a disable novalidate primary key;
Table altered.
SQL> insert into a values (1, 'sd ');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table a enable novalidate primary key;
Alter table a enable novalidate primary key
*
ERROR at line 1:
ORA-02437: cannot validate (SYSTEM. SYS_C001578)-primary key violated
In the pk test, enable novalidate cannot be used.
According to kamus, the enable novalidate primary key must be unique.
I checked the relevant information and did not find the above description, but found the following statement:
Primary and unique keys must use nonunique indexes
Test from new
4. Test pk (2)
SQL> alter table a disable novalidate primary key;
Table altered.
In this case, I checked table a and found that the index pk_a corresponding to the primary key is missing.
Create an index manually
SQL> create index I _a on a (no );
Index created.
SQL> alter table a enable novalidate primary key;
Table altered.
Actually succeeded.
Conclusion:
From the test results, we can see that novalidate works properly when it is not a pk; un.
When using pk; un, you must first create the relevant index, and then use novalidate.