Re: Primary Keys

You are right about Primary Keys and Unique Constraints, but the original post was comparing Primary Keys and Unique Indexes!

Unique indices are passe. Unique Indexes cannot be the target of a Foreign Key reference, and as far as I know the optimizer won't consider them!

Use Primary Keys and Unique Constraints for *the* unique index for a table depending on whether you're fracturing the relational model with NULLS in the PK, and Unique Constraints for Alternate Keys.

I hope this helps.

Sincerely,

Martin Farber
Independent Oracle Consultant "A Jack of all trades and a slave to one."

                        |  NYNEX Science & Technology
                        |  500 Westchester Ave, Rm 1B-23
                        |  White Plains, NY  10604
                        |     -----------------
                        |  email: farber_at_nynexst.com
                        |  Voice: 914/644-2656
                        |    FAX: 914/644-2216

In article 99q_at_crchh327.bnr.ca, vcross_at_bnr.ca (Vince Cross) writes:
>In article <3ob237$foq_at_inforamp.net>, <flemir_at_tdbank.ca> wrote:
>>In comp.databases.oracle gordonh_at_acslink.net.au (Gordon Hooker) said:
>>
>>>
>>>Although a unique index does not allow duplication of a row in the table
 based
>>>on that key, it quite different to a Primary Key. A primary key is a
 conceptual
>>>object used to enforce referential integrity. A unique index is a physical
 

>>>object used to enforce the PK or a Uniqueness constraint.
>>>
>>I am getting confused between the merits of primary keys and unique index.
>>In an Oracle course I took, the instructor recommended using primary keys
>>rather than unique indexes and the Oracle7 Server Administrator's Guide
>>says "it is better to create constraints to enforce uniqueness than it is
>>to use the CREATE UNIQUE INDEX syntax". Somewhere else implied that CREATE
>>UNIQUE INDEX was an obsolete term. However, other people seem to recommend
>>using UNIQUE INDEX.

 (lines deleted)
>>What looks to be the better approach - DEFINE PRIMARY KEY or
>>CREATE UNIQUE INDEXES.
>
>The way I understand it, the only difference between Primary Keys and
>Unique constraints is that a Unique constraints allows NULL values and
>PK doesn't. Both use indexes, both can be the parent of a Foreign Key
>relationship, and both are limited to 16 fields for composite indexes.
>
>Vince
>--
>* disclaimer - My views respresent NT/BNR in every way, NOT!
>NOTE - my real email address is bartok_at_bnr.ca, NOT vcross_at_bnr.ca
>* non-work related email to vlcross_at_aol.com (Having an AOL account doesn't
> make me a complete idiot. Excessive drinking makes me one.)
Received on Thu May 11 1995 - 00:00:00 CEST