Requires Free Membership to View
Yes and no. Yes, it's really the same. No, because there is no such thing as a "key" other than a primary or foreign key.
So a composite primary key is simply a primary key which consists of more than one column. None of those columns is a "key" in its own right. (The only thing to watch out for is that none of them may be null.)
Perhaps the confusion arises from the use of the word "key" to describe what is really an index. An index on a table is totally separate from, and unrelated to, the primary key. It is unfortunate, in my opinion, that the popular database MySQL allows you to use the keyword KEY as a synonym for the keyword INDEX. Why they chose to allow this is anybody's guess. Perhaps they didn't realize the confusion between indexes and primary keys that this was bound to create.
One thing to keep in mind when designing database tables is that an index is a physical construct, and is used for performance. Primary and foreign keys, on the other hand, are logical constructs, and are defined with only one thing in mind -- to ensure the relational integrity of the data.
See also my previous answers, Questions about primary keys 05 April 2002, and What is a primary key? Composite PK? Foreign key? Tuple? 27 February 2002
This was first published in April 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation