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

On 2013-03-13, Wolfgang Keller <feliphil_at_gmx.net> wrote: <snip>
> It's astounding imho that e.g. normalisation seems to be sufficiently
> addressed in database design classes, textbooks etc., while the problem
> of proper unification appears to be quite underrated, even ignored, even
> by lots of computer scientists by education. Which has also been
> confirmed by the number of answers I got in this group that
> apparently totally ignored the issue.

It is inherent in the nature of groups like this that you will get answers from people who genuinely misunderstand the question, wilfully misunderstand the question, or deliberately ignore the question. Sometimes that is all you get, but you have been rather luckier. Unfortunately sifting the answers is your responsibility.

I think in the end the answer is:
  Don't use a surrogate key unless you have to, and then be aware of the   pitfalls. A common pitfall is to forget to include some constraints in   the design which would be there automatically with only natural keys.

  Your concerns about data integrity are a database design issue.

Those people (and systems) that use surrogate keys as a matter of course are usually wrong, not because surrogate keys are inherently bad, but because those people are not aware of the implications for database design of using surrogate keys.

Eric

-- 
ms fnd in a lbry