Q

Primary key enforced by unique index

What does "the primary key of the table is enforced using a non-unique index" really mean?

What does "the primary key of the table is enforced using a non-unique index" really mean?

It means you got that nugget of information (hint: it's wrong) from an unverified source. <grin>

NOTE: If this was, by some remote chance, something that I wrote, then please let me know which article. I'll see to getting it corrected right away. It's obviously just a typo.

A primary key is implemented using a unique index, with a not null constraint.

Why an index? Because an index lookup is the fastest way that the database optimizer has of checking to see whether a particular value already exists in the table. It doesn't check the table, it checks the index, and an index lookup is extremely fast. That's why the database optimizer automatically builds an index when you declare a primary key. So any time you successfully insert a new row, the value of the primary key in the row is also inserted into the primary key index. Then every time you come along and try to insert another value, the database optimizer will first check the index to see if that value is already there. If it is, you get a "duplicate key" error message, otherwise the insert into the table proceeds, which also automatically inserts the new value into the index.

Why a unique index? Because primary key values must, by definition, be unique. Same for the not null constraint. Primary key values must never be null, so the syntax ensures that you can never declare a primary key unless it's not null.

This was first published in February 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close