novalidate constraint problem

I'm trying to understand the use of the novalidate option when creating constraints. The Oracle 10g manual clearly states:

"ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock."

So, if I already have duplicate rows in my table and I create a primary key constraint with novalidate then the constraint should be created as it won't validate the existing data? Wrong. This option seems to be dependant on the deferrable option also, but this doesn't make sense to me.

If I perform the following:

create table test (col1 number not null);

insert into test values (1);

insert into test values (1);

alter table test add constraint pk_test primary key (col1) not deferrable initially immediate enable novalidate;

ORA-02437: cannot validate (WOS_PHILL.PK_TEST) - primary key violated

(Note, I've specifically included all the constraint state options to make it clear what I am trying to do. I realise that they will default to not deferrable and initially immediate and enable if I don't specify them.

However, if I set the constraint to deferrable it is created without the error:

alter table test add constraint pk_test primary key (col1) DEFERRABLE initially immediate enable novalidate;

The only difference between NOT DEFERRABLE and DEFERRABLE is that the former creates a Unique Index and the latter a non-unique index. I've now pretty much answered my own question, but I'm confused at why the documentation doesn't make this clear.

Why would it be possible to specificy novalidate when using not deferrable as the resulting unique index will still still prevent the constraint being created when duplicates exist in the table.

Am I missing something? Received on Wed Mar 12 2008 - 09:13:05 CDT