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

    Requires Free Membership to View

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

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: