Search code examples
sqldatabaserdbms

What is difference between primary key and identity?


What is its use, when both identifies the unique row? Why people are using identity column as a primary key ? Can anyone briefly describe the answer ?


Solution

  • A primary key is a logical concept - it is the means by which you will uniquely identify each record in a table. There are several types of primary key - a natural key uses a data attribute from the business domain which is guaranteed to have the requirements for a primary key (unique, not null, immutability) such as a social security number, a compound key is a key made up of multiple columns (often used in "parent-child" relationships), and a surrogate key is created by the system; it could be an auto-increment, or identity column.

    Identity is a data type. It is very useful for use as a surrogate primary key, because it has all the attributes required. It's unlikely you'd use the identity type for purposes other than as a primary key, but there's nothing to stop you from doing so.

    So, not all primary keys use the identity data type, and not all identity columns are primary keys.