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

This was first published in February 2003

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.



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: