Last month you provided regarding constraints. It took me a while to track this down, but today I learned that in fact you have discovered a code bug. Thanks for reporting this. A bug report has been filed and this will be fixed as soon as possible.
Subject: User comment for Oracle Database SQL Language Reference (b28286):
Move from DISABLE VALIDATE to ENABLE VALIDATE
Date: Fri, 22 Feb 2008 14:15:23 -0800
Book title: Oracle Database SQL Language Reference
Part number: b28286
Release: 11g Release 1 (11.1)
Topic title: constraint
Submitted on: 18-FEB-08
I would like to comment this statement below:
"ENABLE Clause: Specify ENABLE if you want the constraint to be applied to the data in the table."
My point of view has based according to this issue below:
SQL> create table parent (id number constraint pk_parent primary key);
SQL> create table child (id number constraint fk_child_parent references parent);
SQL> insert into parent values (1);
1 row created.
SQL> insert into child values (1);
1 row created.
SQL> alter table child modify constraint fk_child_parent disable validate;
SQL> delete from parent;
1 row deleted.
SQL> alter table child modify constraint fk_child_parent enable validate;
SQL> select * from parent;
no rows selected
SQL> select * from child;
ID ---------- 1
SQL> select constraint_name,status,validated from user_constraints where table_name='CHILD';
CONSTRAINT_NAME STATUS VALIDATED ------------------------------ -------- ------------- FK_CHILD_PARENT ENABLED VALIDATED
In short, according to documentation, ENABLE VALIDATE specifies that all old and new data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid. Therefore, if any row in the table violates the integrity constraint, the constraint remains disabled and Oracle must returns an error, otherwise if all rows comply with the constraint, Oracle must enable the constraint.
Oracle 9i Certified Professional
Diana Lorentz, Documentation Project Manager
Information Development, Server Technologies Division, Oracle
Working off site