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(at)samason(dot)me(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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