Problem solve Get help with specific problems with your technologies, process and projects.

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.

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.