Re: novalidate constraint problem

On 13 Mar, 12:45, "Richard Foote" <richard.fo..._at_nospam.bigpond.com> wrote:
> "Milleniumaire" <milleniuma..._at_hotmail.co.uk> wrote in message
>
> news:f39d33e7-87ae-452f-af57-bdcef9120c8f_at_a1g2000hsb.googlegroups.com...
>
>
>
>
>
> > 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.
>
> or
>
> 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.
>
> Cheers
>
> Richard Footehttp://richardfoote.wordpress.com/- Hide quoted text -
>
> - Show quoted text -

Okay, thanks for that. I think my understanding is clear, it's just the Oracle manuals that aren't telling the full story. It wouldn't be the first time ;-) Received on Thu Mar 13 2008 - 09:25:34 CDT