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

Re: are primary keys always 'needed'

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Serge Fonville <serge(dot)fonville(at)gmail(dot)com>
Cc: postgresql novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: are primary keys always 'needed'
Date: 2010-02-28 17:16:02
Message-ID: E93B93F4-8616-4A23-8AC5-84749581E067@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-novice
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




In response to

Responses

pgsql-novice by date

Next:From: Serge FonvilleDate: 2010-02-28 17:17:59
Subject: Re: are primary keys always 'needed'
Previous:From: Tom LaneDate: 2010-02-28 17:04:42
Subject: Re: are primary keys always 'needed'

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