Re: novalidate constraint problem
- From: "Richard Foote" <richard.foote@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 13 Mar 2008 12:45:38 GMT
"Milleniumaire" <milleniumaire@xxxxxxxxxxxxx> wrote in message news:f39d33e7-87ae-452f-af57-bdcef9120c8f@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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/
.
- Follow-Ups:
- Re: novalidate constraint problem
- From: Milleniumaire
- Re: novalidate constraint problem
- References:
- novalidate constraint problem
- From: Milleniumaire
- novalidate constraint problem
- Prev by Date: Oracle11 bug: EZCONNECT adapter doesn't populate service name
- Next by Date: Re: Do other LDAP server work with Oracle RDBMS as name resolution ??
- Previous by thread: Re: novalidate constraint problem
- Next by thread: Re: novalidate constraint problem
- Index(es):
Relevant Pages
|
