Wednesday, February 9, 2011

Primary keys and Unique keys

Today some philosophical debate on whether one should absolutely have a primary key or not, and if one can use Foreign Key to point to a column which is not a Primary Key.

The answer is that, basically, ANY column can be used as a Primary Key (=something that can be used to identify a record without ambiguity) as long as it's UNIQUE and NOT NULLABLE.
Primary Key is just a convenient term for the developer, but carries no exclusive property of its own.

Specifically on Oracle you might find that the INDEX associated to a PK is CLUSTERED, while for a Unique Key it is not clustered.

No comments: