Skip site navigation (1) Skip section navigation (2)

Re: are primary keys always 'needed'

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: are primary keys always 'needed'
Date: 2010-03-05 21:27:02
Message-ID: hmrsv6$849$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
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. 
 

In response to

pgsql-novice by date

Next:From: Jasen BettsDate: 2010-03-05 22:02:55
Subject: Re: WHERE AND JOIN STATEMENTS
Previous:From: Jure KobalDate: 2010-03-05 19:01:48
Subject: Re: WHERE AND JOIN STATEMENTS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group