Re: Question on Primary Keys

From: Paul Taylor <>
Date: Fri, 3 Nov 2000 14:50:40 -0000
Message-ID: <8tuj5n$q72$>

"Jan Lenders" <> wrote in message news:8sfbu5$n04$
> Reinier,
> Don't get me wrong here; I also disagree with the use of meaningless
> primary keys when you can use other candidate keys which are unique AND
> whose value will never change.

Another thing to be careful of, if you aren't already, is meaningful keys which _appear_ unique at first sight but _aren't_ under certain circumstances, or which _appear_ to be mandatory properties of the object whose details need to be stored but _aren't_ under certain circumstances.

Example 1: (UK-specific) NHS Number. Every person who has contact with the Health Service in the UK has one, they're supposed to be unique. Unfortunately, the computer system which issues NHS numbers in the UK managed to issue a number of _duplicates_. So any system using NHS number as a primary key to a "patient" table is going to be out of luck.

Example 2: ISBN number appears to be a mandatory property of the object "book". Wrong. Speaking as someone who spent some years working in a bookshop before getting into IT, not all books or publications have an ISBN number. Bookshop stock control systems using ISBN number as a primary key are likely to come unstuck sooner or later. Then there's the day when the store management decides to sell chocolate bars as well as books... you get the idea.

There's also the problem of what to do if the meaningful primary key ever _does_ change.

Using example 1 above, I worked in Healthcare IT for a number of years. One of the problems with patient data is that it can be very difficult to get the correct details for a person who's in resus having just been rushed to hospital in an ambulance after being scraped off the road. Consequently, many Hospital systems contain lots of duplicate data for people who've been re-registered because it wasn't possible to find the original registration at the appropriate time. Imagine how difficult it would be to rationalise or merge all these duplicates down to single entries if the database used "meaningful" primary keys.

Bottom line: unless you have a VERY good reason, nonsignificant identifiers are much safer in the long run than meaningful keys.

Paul. Received on Fri Nov 03 2000 - 15:50:40 CET

Original text of this message