Multiple primary keys

database-designforeign keyprimary-key

I am new to database design and have been looking at some example data models here.

One thing I don't understand is if you have a table like client_addresses from the link above:

**************************
 client_addresses
**************************
 PF   client_id
 PF   address_id
 PK   date_address_from
      date_address_to
**************************

Im not quite sure I understand how the keys have been setup. It looks like there are three primary keys. Two are primary and foreign, another is just primary.

Is it the case that there are three primary keys? If so, why is this necessary?

Im using SQL InnoDB. How would I setup a table like this?

Best Answer

The notation that consists of sticking the letters "P" for Primary, "K" for Key and "F" for foreign key, in front of attribute names, is crippled, flawed and confusing.

It is crippled because it suggests that "foreign keys" are keys in a sense similar to "primary keys" and/or "candidate keys". They are not.

It is also crippled because the notation tends to suggest/instill the belief in people's minds that there is somehow an important distinction between "primary" keys and "non-primary" keys. There is not.

It is flawed because it neglects the notion that any attribute can be part of any number of keys. The notation typically allows for an attribute to participate in at most "the" "primary" key and only one other "non-primary" key.

It is confusing because it is both crippled and flawed. Here are the facts about keys :

Foreign keys are not "keys". The term is a blast from the past, and a gross misnomer. They denote an inclusion dependency from the table that the "foreign key" is on, to some other table. There can be any number of "foreign keys" on a table, to any number of other tables, and it is even a fact that the very same set of attributes can be a foreign key twice, or thrice, or any number of times (perhaps to just as many distinct other tables). Heck, the very same set of attributes can even be a "foreign key" twice or more to the very same table (with a different mapping of from/to attributes). Anyhow, any attribute can participate in any number of "foreign key"s.

Primary keys are just keys. There is nothing special about them, in comparison with "non-primary" keys. All of them denote nothing more than just a rule that for the set of attributes they consist of, the combinations of values that appear for these attributes, in the table, must be unique. The fact that most SQL engines force you to declare a primary key, is, once again, a sad and sorry blast from the past. The idea to single out one key and declare that one "more important than others" is merely psychological. It might suggest that this is the preferred key to use for the table at hand. Don't make the mistake of thinking that this constitutes an obligation for other database designers to reference only this key in what tables they design.

Brief : there can be any number of "foreign keys", and any attribute can participate in any of them. There can be any number of keys, and any attribute can participate in any of them.

A notation or a tool that respects these facts, will at the very least allow to identify each key properly (e.g. using labels such as "K1", "K2", ..., "F1", "F2", ...), and allow for each attribute to be accompanied by more than one of these labels (e.g. "K1,K2,F2").

Furthermore, with respect to "foreign keys" specifically, such a tool will also allow to document the exact and complete nature of the "foreign key" : that is, it will also allow to identify the target table of the foreign key and it will also allow to specify/identify what the mapping is like between the attributes of the "source" and the "target" table.

Related Question