RE: Question about unique constraint

It's true that if you create a unique or primary key constraint in the novalidate state and there is no pre-existing index Oracle will create non-unique index, but if a unique index already exists then Oracle will happily use it.  Unless you particularly want a non-unique index supporting the primary/unique key (which would be the case if you wanted it to be deferrable) it's far better to base it on a unique index because there are a number of small but significant differences in workload depending on your choice.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Tefft, Michael J [Michael.J.Tefft_at_snapon.com]
Sent: 29 June 2016 10:59
To: Powell, Mark; napacunningham_at_gmail.com; oracle-l_at_freelists org
Subject: RE: Question about unique constraint

>> I believe you will need to create your primary-key constraint using a  non-unique index <<

>  Why?

Because he did not want the constraint to be validated at the time it was created.

Mike Tefft
--
http://www.freelists.org/webpage/oracle-l

--