Primary keys

2016 (13.x) and later versions

Azure

SQL Managed Instance

Primary keys and foreign keys are two types of constraints that can be used to enforce data

integrity in SQL Server tables. These are important database objects.

A table typically has a column or combination of columns that contain values that uniquely

identify each row in the table. This column, or columns, is called the primary key (PK) of the

table and enforces the entity integrity of the table. Because primary key constraints guarantee

unique data, they’re frequently defined on an identity column.

When you specify a primary key constraint for a table, the Database Engine enforces data

uniqueness by automatically creating a unique index for the primary key columns. This index

also permits fast access to data when the primary key is used in queries. If a primary key

constraint is defined on more than one column, values can be duplicated within one column,

but each combination of values from all the columns in the primary key constraint definition

must be unique.

As shown in the following illustration, the

and

columns in the

table form a composite primary key constraint for this table. This

makes sure that every row in the

table has a unique combination of

and. This prevents the insertion of duplicate rows.

A table can contain only one primary key constraint.

A primary key can’t exceed 32 columns and a total key length of 900 bytes.

The index generated by a primary key constraint can’t cause the number of indexes on

the table to exceed 999 nonclustered indexes and 1 clustered index.

If clustered or nonclustered isn’t specified for a primary key constraint, clustered is used if

there’s no clustered index on the table.

All columns defined within a primary key constraint must be defined as not null. If

nullability isn’t specified, all columns participating in a primary key constraint have their

ProductID
VendorID
Purchasing.ProductVendor
ProductVendor
ProductID
VendorID