Re: identity columns

"--CELKO--" <71062.1056_at_compuserve.com> skrev i en meddelelse news:c0d87ec0.0201091009.784dc71b_at_posting.google.com...
> >> As an example you might think of records of invoices, which may
> have a
> natural candidate key combined of foreign keys to Customer and Order.
> Nevertheless, a running integer is often used as a primary key, eg. to
> document (for the auditor) that no sale is forgotten. <<
>
> The reason for sequential numbers was that they were printed on the
> paper order forms, so they existed before the actual order. The
> auditor wanted to be able to go to the PHYSICAL document for each
> order in the PHYSICAL filing cabinet.

A sequential numbering is still used, regardless of physical storing of documents in a filing cabinet or not.
The number (the sequence) is primarily used to ascertain that no sale is left unaccounted for or deleted from the records. If an invoice number 12 and number 14 is registered, so should an invoice number 13 be registered. It thus serve an additional purpose than to order and identify: it reflects a "business rule". This rule can not be reflected by the "natural key", unless a strict 1-1 is demanded between orders and invoices. I think this may be your assumption, as you refer to the pre-printed order forms. In that case the same rule applies to orders: the order number should be sequential with no gaps. I do agree that it is a matter of opinion to regard the running number as a surrogate key or as a natural key, as it originates from a conceptual number "printed on a paper form". I chose to call it a surrogate key because it is "invented" due to lack of a natural key (whatever natural could be by orders and invoices...). The practical consequence and advise will be the same: use the running integer as the key. Whether a concrete DBMS has a usable built-in function for autogenerating such numbers or not is another matter - a matter of physical design. Received on Fri Jan 11 2002 - 08:39:24 CET