Re: NextNumbers Tables

From: Daryl Richter <>
Date: 18 Feb 2003 10:39:29 -0500
Message-ID: <>

Alan Gutierrez <> writes:

> Pablo Sanchez wrote:
> > (Saul Margolis) wrote in
> >
> >
> >>This is exactly the way I try to do things, having a surrogate key
> >>and natural key with a unique index.
> >>Ever tried tracing a transaction manually (as one is sometimes
> >>forced to do) when you only have a natural key consisting of 10
> >>fields?
> > Exactly! This is why when people jump up and down about how surrogate
> > keys shouldn't be used, I shake my head and realize that these folks
> > have never worked on a large production environment.
> Would someone with experience using natural keys in a large production
> environment please chime in. I'd like to hear how using natural keys as
> primary keys in a large production environment makes things faster,
> easier, and better.

I design and manage databases in a medium-large (>50 G total data, at least a thousand tables over 5+ databases) 24x7 environment and I would say that the answer is "it depends." :)

You need to balance the convenience and simplicity of the surrogate keys against their brittleness. The two main problems I find with them are:

  1. They tend to get out of sync between development, test, and production. This in itself isn't an issue except:
  2. They tend to "leak" out into the application programs and before you know it you have hard-coded [fooIds] everywhere. Because of this I now disallow direct table access and only allow select through views which do not contain the surrogate key. In other words, the surrogate key is a physical implementation detail/issue and not part of the logical model exposed to programmers.

I also always define the true candidate key as an alternate key. I fully agree that a table with its only key as a surrogate key is a lurking problem.

> Or just a pointer to a strong argument.
> Alan Gutierrez -


Daryl Richter
Technical Manager, EAI Group
Exelon Power Team
Received on Tue Feb 18 2003 - 16:39:29 CET

Original text of this message