Comparison of PRIMARY Key and identity (1,1)

PRIMARY KEY Constraint

A table often has a combination of columns or columns whose values uniquely identify each row in the table.

One or more of these columns is called the primary key of a table, through which you can enforce the entity integrity of a table.

You can create a primary key by defining a PRIMARY key constraint when you create or change a table.

A table can have only one PRIMARY key constraint, and a column in a PRIMARY KEY constraint cannot accept null values.

Because the PRIMARY KEY constraint ensures unique data, it is often used to define identity columns.

If the PRIMARY key constraint is defined on more than one column, the values in one column can be duplicated, but the values of all the columns in the PRIMARY KEY constraint definition must be unique.

IDENTITY (properties) (Transact-SQL)


Create an identity column in the table.
This property is used with the CREATE table and ALTER table Transact-SQL statements.

The identity property differs from the SQL-DMO identity property, which provides the row identity property of the column.

Scope of application: SQL Server (SQL Server 2008 to current version).

Grammar


IDENTITY [(seed, increment)]
Parameters
Seed
The value used by the first row loaded into the table.


Increment
The increment value that is added to the identity value of the previous loaded row.

Both the seed and increment must be specified, or neither is specified. If neither is specified, the default value (1,1) is taken.

Summarized as follows:

Primary key is a primary key, typically used in a column, whose value cannot be null and has a unique value. can also be used with multiple columns, and the combined values of multiple columns are unique.

Identity is the attribute, and the self-added column. The field of the IDENTITY cannot be assigned by default in the INSERT statement.