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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation