Re: are primary keys always 'needed'

On Feb 28, 2010, at 11:38 , Serge Fonville wrote:

> Hi,
>
> I was wondering if primary keys are always needed.
> What I understand;
> - The value of a primary key is unique per table
> - Primary keys are unique and not null.

Yes, in the sense that according to relational theory, a relation
(table) contains unique rows, each of which is (uniquely) identified
by its key. Note that a key is not necessarily a single attribute
(column): a key may be a "compound" key made up of a number of columns.

> I have a couple of tables.
> Some have a serial that is used as a foreign key in another table.
> Some tables consist of a combination of two foreign keys (that are
> unique together) and a field that is uniquely related to that
> combination (but is not necessarily unique within the table)
> Others have no unique field or combination at all

this "two foreign keys (that are unique together)" is an example of a
compound key.

>
> For the tables that are only used in the foreign part of the
> relationship(s), is there an added value for user of a surrogate
> primary key, which will never be referenced.

Most likely not. Note this question (is there added value of a
surrogate primary key?) is different from your initial question: (are
primary keys always needed?). Do not confuse primary keys with
surrogate keys (often generated by an auto-incrementing value such as
a serial). A surrogate is a meaningless attribute used only to
uniquely identify the row. If you *do* use a surrogate key, you should
also identify (and enforce) the "natural" (perhaps compound) key of
the table. Otherwise you may very well get duplicates of the "natural"
data even though the meaningless surrogate key is unique across the
table.

> I did a lot of googling and found very varying opinions.
> Mainly:
> - Always use a primary key, no reason why, it's just 'better'
> - Use primary keys when it makes sense.
>
> How do I determine what 'sense' or 'better' mean?

This depends on your application. Here are a few things to think about.

* Surrogate keys require joins or lookups to retrieve the actual data
values they represent.
* Surrogate keys require additional space in the table that holds the
actual data values and an extra index to enforce the uniqueness of
these values.
* Surrogate keys are often smaller in terms of size on disk than the
data they refer to. This counts for both tables including the
surrogate key and the indexes that include them.
* Integer surrogate keys used to represent text data are often faster
to look up, as integer comparisons are faster than string comparisons.

This is not an exhaustive list. Some people are vehemently opposed to
surrogate keys; others use them blindly. I think there are cases where
using surrogate keys does make sense, taking into account the trade-
offs when using them.

Hope this helps.

Michael Glaesemann
grzm seespotcode net