Re: novalidate constraint problem

From: Richard Foote <>
Date: Thu, 13 Mar 2008 12:45:38 GMT
Message-ID: <Sl9Cj.25203$>

"Milleniumaire" <> wrote in message
> 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?

Hi There,

I don't think you're missing much except if you really want to create a non-deferrable, novalidate constraint, you have a number of options:

  1. create the non-unique index *before* creating the constraint:

SQL> create index test_i on test(col1);

Index created.

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

Table altered.


2) create a *non-unique* index *at the same* time as the constraint:

SQL> alter table test add primary key (col1) using index (create index test_1 on test(col1)) enable novalidate;

Table altered.


Richard Foote Received on Thu Mar 13 2008 - 07:45:38 CDT

Original text of this message