Difference between disable/enable validate/novalidate

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.