Re: PK vs Unique index

Phil,

I agree completely with Steve's point about nulls.

However, if you're *not* allowing nulls, then, using Oracle, AFAIK the physical implementation is equivalent; i.e. (as you are aware) Oracle automatically creates an unique index to enforce a declared primary key constraint; thus (other things being equal) I wouldn't expect performance to
differ whichever you choose.
But I can think of at least one disadvantage in using unique indexes as opposed to primary key constraints; you lose the ability to create snapshots "with primary key" (which is much better IMHO than "with rowid"). Also, a declared constraint seems to me to be slightly more elegant than a created
index. As an Oracle man, I'd prefer the constraint. Doubtless there are other advantages; conversely there may be disadvantages if DDL
portability among different db vendors is a factor (Nuno, is this why Peoplesoft uses unique indexes rather than PK constraints?).

Not comprehensive; just my 2c..

By the way, no offence, but please consider whether it's absolutely necessary to crosspost. I suggest that a post to c.d.o.s. alone would have elicited a decent set of responses.

Paul

"Stephen B" <stephen.bell_at_cgi.ca> wrote in message news:6Y_08.10539$o_.1854247_at_news20.bellglobal.com...
> Hi Phil,
>
> At the risk of stating the obvious, a unique index will allow NULL
> values...I'd only be guessing about performance..I'm just saying that
> you're comparing two different functionalities (one with unique/no nulls
and
> one with unique only)..
>
> It seems to me that it's more a question of what you require than a
> performance issue...
>
> I hope this helps,
>
> Steve
>
> "Philip Morrow" <cracker_at_mymorrow.com> wrote in message
> news:kQW08.45721$_p.11054374_at_typhoon.tampabay.rr.com...
> > Is there any performance difference is you use a PK constraint vs just
> > creating a unique index. I know the PK will create the unique index as
> > well, but is there a disadvantage to creating just a index and not a PK
> > constraint
> >
> > Thanks
> > Phil
> >
> >
>
>
Received on Wed Jan 16 2002 - 13:45:03 CST