Re: UNIQUE &NOT NULL vs PK

A copy of this was sent to Nandakumar <N.Kumar_at_rocketmail.com> (if that email address didn't require changing) On Mon, 08 Nov 1999 16:57:51 GMT, you wrote:

>could someone list out the advantages/disadvantages of defining a column
>with PRIMARY KEY constraint over UNIQUE and NOT NULL constraint?
>
>i am wondering where the diff. between these two exists?
>
>... in storage of the constraint ???

no, you can control a primary keys index just the same as a unique constraints index.

>... ability to be REFERENCEd in foreign tables ???

no, you need to either be a primary key or have a unqiue constraint to be referenced, either will do.

>... INDEXabilty ???

no, both a primary key or unique constraint will use an index and that index may or may not be a unique index (as constraints can be deferrable).

>... or something else ???
>

the main reason is documenation and understandability of the data model.

a primary key constraint says something about the columns in the constraint. a unique constraint says something else about those columns.

A primary key "says" more about the columns involved and implies a UNIQUE constraint plus a NOT NULL constraint. Not so the other way around as you may have UNIQUE + NOT NULL column(s) in the table that are not the primary key (surrogate keys maybe but there is only 1 primary key).

>thanks in advance for your valuable responses.
>regards
>Nanda
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 08 1999 - 13:11:17 CST