Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

On Thursday, March 14, 2013 1:38:36 AM UTC+2, Jan Hidders wrote:
> Sure. But that does not mean that all arguments in favor of surrogate
> identifiers are therefore complete nonsense, even if most of the people
> that favor them tend to be crappy data modelers with a weak grasp of
> the importance of having proper integrity constraints. :-) I'd like to
> see a bit more precise analysis on when and why they are a problem,
> rathern then a naive sweeping generalization that declarese them as
> always evil.
>
> -- Jan Hidders

I summarized my pros and cons on surrogates: Pros:

- Stability
- "Convenience" to use
- Performance (?)

Cons:

- Difficult to synchronize with external systems.
- You need more joins (performance?)
- You lose the simplicity and power of overlapping composite foreign keys constraints (discussed in this thread).
- In some cases you lose the ability to manipulate data as a set (see bellow*).

• If you need the newly allocated value you have to process data one row at time and use some system functions such as "get_next" and "get_current". Things get complicated if you need the value twice, because you have to use "get_next" first time and "get_current" further on. Since you can’t control the order of evaluation of expression terms you have to rely on some procedural language.

If you have an alternate natural key, you can manipulate data as a set, but have to do it in two steps, first insert, then retrieve the surrogate values by using the natural key. In practice you may also need a temporary table to avoid constraint violations.

As can be seen, two "pros" are disputable, and two "cons" are very subtle (and many users may never encounter in practice). So ultimately is a matter of tradeoff. Received on Thu Mar 14 2013 - 11:55:42 CET