Is there a difference between Primary key and UNIQ Clustered index?

For example, if you create on the UNIQ Clustered index column, then it is not perceived by the studio as a primary key.

  • 2
    for a good key is just a declaration, which is implemented as an index. those. an index for the key will be built. But the key itself will not be announced if there is only an index. there is no particular difference, though without a declared key, it is unlikely that you will be able to make foreign reference to it - Mike
  • The primary key, in addition to the functions of the unique index, carries the function of uniquely identifying the record (primarily for the integrity monitoring subsystem). - Akina

1 answer 1

There is a difference. First of all, the semantic one — PK is the key of the string, i.e. a value that directly refers to a specific string, while a unique index is a restriction of the uniqueness of a specific set of fields (not necessarily the key of a field).

Example: In the table physical. Name and date of birth may be the primary key of the individual (although it’s not the fact that this is correct, these data may, in theory, not be unique), but the passport number and insurance certificate number may have a unique index (since they must not repeat), but not be the primary key.

In addition, the implementation difference is as follows:

  1. by default, PK has a clustered index, and UI is a nonclustered
  2. UI can have NULL values, PK cannot.
  3. A table can have only one PK, but an unlimited number of UIs.
  4. Difference in creation syntax.

The information was partially taken from here, you can get acquainted with the original: http://sqlhints.com/2013/06/02/difference-between-primary-key-and-unique-key-in-sql-server/