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

Re: surrogate vs natural primary keys

From: Seb <spluque(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: surrogate vs natural primary keys
Date: 2008-09-17 00:10:19
Message-ID: (view raw or whole thread)
Lists: pgsql-sql

After some more reading and considering your feedback, I'm still
somewhat confused about this issue.

1. Should the choice of surrogate/natural primary keys be done across an
entire database, or does it make more sense to do it on a per-table
basis?  I reckon one could do it on a per-table basis, but its direct
relationships would influence the choice.

2. If we do find a suitable natural primary key for a table, but it
turns out to be a composite one, how can such a key be referred to in
another table?  Say we have:

    c1 varchar(200),
    c2 int8,
    c3 varchar(500),
    PRIMARY KEY (c1, c2)

and I want to create a table t2 which needs to refer to the composite
primary key of t1.  Should one create 2 columns in t2 that REFERENCE c1
and c2?  If so, this seems very cumbersome and I'm tempted to create a
surrogate key in t1 just to be able to refer to it more efficiently.  Is
this something we should be considering when choosing natural
vs. surrogate keys?  Thanks again.


In response to


pgsql-sql by date

Next:From: Scott MarloweDate: 2008-09-17 02:34:51
Subject: Re: surrogate vs natural primary keys
Previous:From: Richard BroersmaDate: 2008-09-16 15:30:55
Subject: Re: a simple transform

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