Re: surrogate vs natural primary keys

On Tue, Sep 16, 2008 at 6:10 PM, Seb <spluque(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>