Restrições de Integridade no Oracle: Alterar do estado de DISABLE VALIDATE para ENABLE VALIDATE. Bug confirmado.

Olá,

Com relação ao artigo publicado em 19 de Fevereiro de 2008 intitulado "É possível que uma restrição esteja no estado ENABLE VALIDATE e ainda assim mesmo permitir dados existentes que violem a restrição?", e pelo fato de no mês passado eu ter postado um comentário na página da Oracle relatando sobre o problema, hoje recebi uma resposta por e-mail da Divisão de Tecnologia da Oracle. Enfim, parece que realmente foi confirmado um bug como demonstrado no e-mail abaixo:

Hello Eduardo,

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.

As I am in the documentation group, I will not be able to track the progress for you. However, the README associated with each release of the database lists known bugs, so if necessary you could track the bug that way. Let me know if you want the bug number -- I can get that for you.

Regards,
Diana

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
From: [confidencial]
To: [confidencial]
CC: [confidencial]

Submitter: legatti
Book title: Oracle Database SQL Language Reference
Part number: b28286
Release: 11g Release 1 (11.1)
Topic title: constraint
URL: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/clauses002.htm
Status: Forwarded
Submitted on: 18-FEB-08


Hi,

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);

Table created.

SQL> create table child (id number constraint fk_child_parent references parent);

Table created.

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;

Table altered.

SQL> delete from parent;

1 row deleted.

SQL> alter table child modify constraint fk_child_parent enable validate;

Table altered.

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.

So, how is it possible that a foreign key constraint that has the status ENABLE and state VALIDATE, but in fact, the current data inside the table violates the integrity constraint? Therefore, the ENABLED VALIDATE state must ensure that all incoming and existing data conforms to the constraint. In my point of view whenever a foreign key constraint is moved from the DISABLE VALIDATE state to the ENABLE VALIDATE state, all data must be checked. It does make sense?

Regards.

Eduardo Legatti
System Analyst/DBA
Brazil/Belo Horizonte
Oracle 9i Certified Professional

--
=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/
Diana Lorentz, Documentation Project Manager
Information Development, Server Technologies Division, Oracle
Working off site

Read more >> Options >>