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: 87ljxrvdhw.fsf@patagonia.sebmags.homelinux.org (view raw or flat)
Thread:
Lists: pgsql-sql
Hi,

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:

CREATE TABLE t1 (
    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.


-- 
Seb


In response to

Responses

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-2014 The PostgreSQL Global Development Group