Re: Primary Key of a table

Michael Kustermann <MKUSTERM_at_worldnet.att.net> wrote:

>If a user wants to limit rows by a certain
>criteria which would be in your Natural primary
>key, then the query is simple. However, if you
>use Artificial keys and eliminate the Natural
>columns in foreign tables, and the user wants
>to execute the aforementioned query, then in
>your application, you have to first query the
>master table to identify all correct primary
>key Numbers, THEN requery your detail table
>using the Artificial numbers you found.

There's a misconception that I'd like to clearup.

Once you've sufficiently normalized a table (either to the third form, Boyce-Codd, or fourth and beyond) your table looks like this:

<ck1>, <ck2>, ... <ckn>, <data1>, <data2>, ....

(Here, its understood that the candidate "keys" could very well be made of two or more items.)

The ck's are what's termed 'candidate keys" -- any one of which can serve as the primary key for the table. Let's say you pick <ck2> as your primary key. Then your table looks like:

<ck2>, <ck1>, ... <ckn>, <data1>, <data2>, .... pk ak1 akn

With table constraints:

  ck2 is the primary key
  ck1 is not null and unique
  ck3 is not null and unique
  ....

There is no need to banish the alternate keys to some other status (as being foreign keys, say). The alternate keys have independent existence on their own right.

Let's now add a sequence generated key with the intention of making it the primary key:

<sk>, <ck2>, <ck1>, ... <ckn>, <data1>, <data2>, .... pk ak' ak1 akn

Now the table constraints are:

  sk, the artifical sequence generated item, is the primary key   ck2 is not null and unique (which it was when it was a pk)   ck1 is not null and unique
  ....

The real trick is to hide the artificially generated key from the users as much as possible and to allow users to reference rows by the (more) natural keys (which are meaningful to them).

aloha
ayn Received on Mon Apr 01 1996 - 00:00:00 CEST