Re: novalidate constraint problem

"Milleniumaire" <milleniumaire_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 Foote
http://richardfoote.wordpress.com/ Received on Thu Mar 13 2008 - 07:45:38 CDT