ENABLE NOVALIDATE and Unique index question

[Chris McFadden]

I am trying to create a unique constraint on a table but I do not want
to clean up existing data. I only want the constraint enforced for
future inserts and updates. I am using Oracle 8.1.5. After the second
statement I get the error:
ORA-02299: cannot validate (UNIQ_CONS1) - duplicate keys found
Why is Oracle trying to validate the constraint even when NOVALIDATE is
being used? Any suggestions?? Thanks, Chris

ALTER TABLE mytable
ADD CONSTRAINT uniq_cons1
UNIQUE( email, name, name ) DISABLE;

ALTER TABLE mytable
MODIFY CONSTRAINT uniq_cons1 ENABLE NOVALIDATE;

Sent via Deja.com
http://www.deja.com/

[Jonathan Lewis]

Add the constraint as
deferrable initially deferred
then Oracle will create the underlying index
as a non-unique index.

(Chapter 18 - Integrity)

--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

Chris McFadden wrote in message <95celg$7jk$1...@nnrp1.deja.com>...

[Howard J. Rogers]

The problem lies in your initial creation of the constraint. If you just
say you want a Unique constraint, Oracle will create a unique index to
enforce it. If you say you want a unique constraint DEFERRABLE INITIALLY
<X> then Oracle will enforce it with a non-unique index.

Since, in your case, you allowed it to create a unique index, then the
*Index* is taking it upon itself to spit the dummy about the violating
records, and the novalidate at table level is having no effect.

To ensure non-unique indexes are used, the key word is DEFERRABLE (but
you'll have to check how to spell that, because I don't have my Big Boy's
Book of Syntax ready to hand!). Whether the constraint is then ACTUALLY
deferred or is initially IMMEDIATE is irrelevant.

Regards
HJR

"Chris McFadden" <chris_m...@discovery.com> wrote in message
news:95celg$7jk$1...@nnrp1.deja.com...