Re: surrogate vs natural primary keys

On Tue, Sep 16, 2008 at 6:10 PM, Seb <spluque@gmail.com> wrote:
> 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.

Definitely on a per table basis.  for instance, if you create a lookup
table to use as a target for an FK, there's usually little need for an
artificial key.

> 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)
> );

create table t2 (   d1 varchar(200),   d2 int8,   d3 varchar(1000),   foreign key t2_fk references t1(c1,c2) );

or something like that.

>
>
> 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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>