Re: PRIMARY KEYS

On Tuesday, May 20, 2003, at 08:41 AM, Karsten Hilbert wrote:

>> [Earlier post] A primary key is a unique way to identify one row in a
>> table.
>
>> to have several keys, but only one would be your primary key. For
>> example,
>> here in the UK, each adult has a different National Insurance number.
>> If you
>> were writing a payroll application, you might have an internal
>> "payroll_id"
>> column as well as a "ni_num" column. Both are unique but it might
>> make more
>> sense in the application to use "payroll_id" - this is a business
>> decision. [Earlier post]
>

> And - if you agree with database theory - a bad one at that.
> Supposedly primary keys should be void of any meaning bar
> their primary key-ness. [snip]

I think a database "theory" that says primary keys should be void of
any business meaning is a bad theory, and is certainly not required by
the relational model.

As one example, a primary key with business meaning assures that one
does not have duplicate records (if the data model is otherwise
correct).

If the data being modeled does not have a "natural" candidate key (an
attribute value or values that will always be unique), then one should
be created (such as a National Insurance number).

Such a created primary key usually becomes indistinguishable from a
"natural" key as a business matter because the ability to distinguish
entities in the real world is also useful for many purposes outside of
any particular database.

Another reason to create a primary key would be to avoid overly complex
composite keys, particularly those that require attributes that are not
otherwise important for the data model -- for example, an adult could
be uniquely identified by a composite key including name, date of
birth, place of birth, parent's names, etc., without using the National
Insurance number (or some other formerly created key).