Does one element of a composite primary key still act as an index? I'm just doing a database tuning exercise. My...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.