Re: Artificial Primary keys

In message <c0d87ec0.0202061353.cf26ce5_at_posting.google.com>, --CELKO-- <71062.1056_at_compuserve.com> writes
>>> There is no real difference between an identity column and an
>invoice-number or SSN. All of them are artificial. <<
>
>I see a large difference among these things.
>
>The IDENTITY column is a system generated number that I cannot
>reproduce on another machine. When I look at one, I have no idea if
>it is meaningful or not -- remember you can get gaps in the sequence.

If you look for meaning in an IDENTITY column then you haven't understood its function. It is an identifier, there is no hidden meaning. Gaps in the sequence, or even whether the numbers are sequential, have no great significance.

I recently had to invent a new coding system. I deliberately made it difficult to read any significance into the numbers. I issued the numbers in small blocks with gaps between them so that it would not be possible to tell which numbers had been issued and which had not. That stopped people looking for the missing numbers in the sequence.

>
>With an invoice number, I have a paper copy of that number in my hand
>when I go back to the store and bitch about the merchandise. There is
>a document for it which is external to the database.

Not all invoices exist on paper, many are purely electronic documents. The number is nothing more than and IDENTITY that has been published outside the organisation that issued it. The function of the number is distinguish between documents which might otherwise have identical data. I might break an order into two equal shipments. Without an artificial key the data printed on the invoices could be identical. To store their data in a database I would either have to create an artificial key or accept that my invoice table can legitimately have duplicated records.

>
>The SSN is maintained by the United States Federal government and has
>documentation going back for all the years the person to whom it is
>attached has been alive. We issue them at birth now.

The SSN is an IDENTITY value generated and published by a trusted third party. Its function is to distinguish between people who might otherwise have no distinguishing features. An IDENTITY function is probably the best way of generating this sort of number. Here in the UK we have a National Insurance number that has the same function. There is data embedded in the code that identifies the time and place of birth. Because of this there have been instances of the same code being issued to two people born in the same hospital on the same day. It would have been much better to use an IDENTITY function and issue a code that is guaranteed unique but has no other significance.

The principle difference between the identity function and the invoice/SSN is that the IDENTITY value is not published outside the organisation that generates it.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti