Ask the Expert

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?

    Requires Free Membership to View

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


This was first published in February 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: