Problem solve Get help with specific problems with your technologies, process and projects.

More on composite primary keys

The popular definition for 'Composite Primary Key' is 'A primary key consisting of more than one column.' Is this the same as saying 'A primary key consisting of more than one key?'

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

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.