Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
It's a composite key. I'm no InnoDB expert (or even user) but unless it is much different from traditional RDBMSes, a table can only have one PK. In this case it looks like multiple columns make up the one PK on this table. This is a composite key. –  Thomas Stringer Feb 19 '13 at 11:36
    
thanks. Just had a google and it seems that relationship tables often have composite keys. –  Gaz_Edge Feb 19 '13 at 11:58
    
@ThomasStringer do you want to move your comment to an answer? –  Mr.Brownstone Feb 19 '13 at 12:37

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
thanks for the detailed answer - a bit confusing in places, but I think i understand your points. Im not quite sure I agree with you on primary key not being more important. In SQL InnoDB, the primary key is also the cluster key. There is no other way to defined a cluster key (so ive been told) therefore primary key is more important. –  Gaz_Edge Feb 19 '13 at 13:37
    
@Gaz_Edge: But that's only in current implementation of InnoDB. And only when a Primary Key is defined. When not, the first Unique index is the clustered index. And when no Unique index is defined, a hidden column in created and used as the clustered index. But all those may well change into a future version of MySQL. –  ypercube Feb 19 '13 at 14:14
2  
MySQL's odd implementation decisions don't dictate what is and isn't good relational modeling. The way it implements clustering keys is pretty brain-dead. Compare it to SQL Server's implementation, for example, which is both more explicit and more flexible. –  Mike Sherrill 'Cat Recall' Feb 19 '13 at 14:15
    
And @Erwin's point (I thinK) is that all these (implementation specific) details are just that, implementation specific. There is no real difference between a Primary Key and a Unique constraint. –  ypercube Feb 19 '13 at 14:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.