Re: novalidate constraint problem

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: http://www.psoug.org/reference/constraints.html That begins with:
ALTER TABLE uclass ENABLE NOVALIDATE PRIMARY KEY;

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org