Sunday, July 29, 2007

Primary Key vs. Unique Key

There are two differences between Primary key and Unique Key:

  • First and a trivial difference is that Unique Key would permit null value (one null value per cloum in the unique key constraint), while Primary key would not allow for null value.
  • Second and the major difference that you should know is that the Unique Key indexes are non-clustered while Primary key is clustered index. This difference has following corollaries

    • There could be only one PK per table emanating from the fact that there could be only one clustered index on a table.
    • The PK affects how the data is physically stored but a unique key doesn't.
Now the question is what is a clustered index and why there can be only one clustered index on a table. Clustered index is a index with leaf node containing actual data and not pointer to the actual data while for Unique Key index, leaf node contains pointer to the actual data storage.

No comments: