Showing posts with label DB. Show all posts
Showing posts with label DB. Show all posts

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.