Composite primary keys and indexes

Does one element of a composite primary key still act as an index? I'm just doing a database tuning exercise. My hunch is the answer is no, on the grounds that one element of a CPK no longer equals a PK, so why should it continue to work as an index?

A primary key is not an index, though all database management systems of which I know implement unique indexes to enforce the primary key constraint. A composite primary key would therefore result in an index of multiple columns. As with all B-Tree style indexes, the columns of a composite index are ordered.

If you had an index of LAST_NAME, FIRST_NAME (primary key or not) in that order, and your query includes a where clause that states LAST_NAME = 'SMITH' and FIRST_NAME = 'JOHN', then the index would probably (almost definitely) be used. In the case that you only stated LAST_NAME = 'SMITH', the index would probably be used as well, as LAST_NAME is the first element in the composite index. If in the case you only used FIRST_NAME = 'JOHN' in the where clause, the index would most likely not be used. To answer your question, when using the first n elements of an index in a where clause, the DBMS will most likely use the index to retrieve the result set, even if not all the columns of the index are specified.

For More Information

• Dozens more answers to tough SQL questions from Jason Law are available here.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.