Re: Primary Key - Mailing list pgsql-general

On Fri, Nov 16, 2007 at 04:41:42PM -0500, Merlin Moncure wrote:
> On Nov 16, 2007 3:21 PM, Sam Mason <sam@samason.me.uk> wrote:
> > On Fri, Nov 16, 2007 at 12:06:46PM -0800, Joshua D. Drake wrote:
> > > When that is needed I do this:
> > >
> > > create table foo(id serial unique, a text, b text, primary (a,b));
> >
> > Humm, so the other way around from what I've ended up doing.  I'll need
> > to think about the implications of changing things around like this.
> > There are lots of things that seems as though they'll be pretty awkard
> > to do, I'm sure it's just because I haven't thought about it enough.
>
> there is a lot of nuance to this debate and tons of hyperbole on both
> sides.  There are many side effects, pro and con, about choosing
> 'natural' keys vs. surrogates.  josh's suggestion is the most
> reasonable compromise, because it allows you to get the performance
> benefits (which are sometimes overrated) when you need it,

I'm not sure if performance has ever really come into the decision about
whether to use natural/surrogate keys with me.  The main reason for
using a surrogate key is simplicity; I don't trust myself to maintain a
large database where every relationship is composed of multiple columns.

If I could say somewhere that I want a set of properties to hold (i.e.
there is a 1-to-1 relationship between these tables, there's at most one
row in this table for each of these, etc) and then these constraints
were checked when I actually wrote my queries I'd be much happier.

For example, given the tables:

  CREATE TABLE foo (
    id INTEGER NOT NULL PRIMARY KEY,
    description TEXT );
  CREATE TABLE bar (
    key INTEGER NOT NULL PRIMARY KEY,
    fooid INTEGER NOT NULL REFERENCES foo );

I'd like to be able to write the query:

  SELECT b.key, f.description
  FROM bar b, foo f
  WHERE b.fooid = f.id;

And be able to say that I expect exactly one row for each bar.key.
The database would be able to go back over the definitions and prove
that this constraint holds (because bar.key and foo.id are UNIQUE, the
FOREIGN KEY constraint has checked that bar.fooid always references a
valid foo.id, and that bar.fooid can never be NULL).

Or is this the sort of thing that materialised views are good for, and
I've always just been thinking about them as a performance hack.

> the sad fact is that sequences have made
> developers lazy, not giving much thought to proper normalization
> strategies which in turn often produces lousy databases.  if you know
> how to do things properly, you will know what we mean.

"Properly" is very open ended.  Most people will try to do their best
job (given various external constraints) and we've all experienced bad
design, if only from stuff that we did while learning.  I think I've
experienced this, but you've probably got a very different idea about
what "properly" means than I do.


  Sam