Re: are primary keys always 'needed'

On 2010-02-28, Serge Fonville <serge(dot)fonville(at)gmail(dot)com> wrote:
> Hi,
>
> I was wondering if primary keys are always needed.

The answer is no. but they are usually a very good idea.

> What I understand;
> - The value of a primary key is unique per table
> - Primary keys are unique and not null.
>
> I have a couple of tables.
> Some have a serial that is used as a foreign key in another table.
case (a)
> 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)
case (b)
> Others have no unique field or combination at all
case (c)

> 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.

In case (b) you can use the pair of external key columns as the primary key.

> I did a lot of googling and found very varying opinions.

> - Always use a primary key, no reason why, it's just 'better'

Some software requires a single-column primary key.

> - Use primary keys when it makes sense.

> How do I determine what 'sense' or 'better' mean?

first you need to understand what the data and relationships represent.

If you never need to uniqely identify a record you don't need a
primary key.

consider the following table fragment

mapref | placename
---------+----------
10,10 | New York
10,10 | Noo Yawk
10,10.01 | New York
10,10 | New York

you may or may not want to allow data with repeats like that.
and if you want to delete or update only one of the ('10,10','New York')
rows you'll neet to take special care.