Re: Primary Key

On Fri, Nov 16, 2007 at 12:06:46PM -0800, Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Fri, 16 Nov 2007 20:00:29 +0000
> Sam Mason <sam@samason.me.uk> wrote:
>
>
> > > > Normally a primary key would just be a single column.  When
> > > > you start going to that many I'd probably have a serial column
> > > > as the primary key, and a UNIQUE index on those six fields.
> > > > Depends on what you're doing, though unless you've got a few
> > > > years experience I'd be tempted to stay away from primary keys
> > > > of more than a single column.
> > >
> > > Fie on you evil synthetic key lovers.  Long live the Natural
> > > Key!
> >
> > Really?  I started off with everything using sequences and
> > everything was good.  Then I found I wanted to do more complicated
> > things so I started to transition to natural keys and things were
> > better.  Then I took things too far and wanted something
> > artificial back in my
>
> When that is needed I do this:
>
> create table foo(id serial unique, a text, b text, primary (a,b));

By itself, this insufficiently restricts what's going on in the table.
I'd recommend a TRIGGER that disallows updating the synthetic key.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate