Re: novalidate constraint problem

From: DA Morgan <>
Date: Wed, 12 Mar 2008 09:29:28 -0700
Message-ID: <>

Milleniumaire wrote:
> 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?

You are misunderstanding what you are seeing. One difference between a deferrable and non-deferrable primary key constraint is that the regular constraint uses a unique index and the deferrable uses a nonunique  index.

A different example would show a different result. Try the example here: That begins with:

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Wed Mar 12 2008 - 11:29:28 CDT

Original text of this message