Create and modify the primary key constraint

You can create a single primary key constraint when creating a table as part of the table definition. If the table already exists without the primary key constraint, you can add the primary key constraint. A table can have only one primary key constraint.

If the primary key constraint already exists, you can modify or delete it. For example, you can make the table's primary key constraint reference other columns and change the column sequence, index name, clustering option, or fill factor of the primary key constraint. However, you cannot change the length of a column defined using the primary key constraint.

Note:
To modify the primary key constraint, you must first Delete the existing primary key constraint and then recreate the constraint with the new definition.

When you add a primary key constraint to an existing column in a table, the database engine checks the data and metadata of the existing column to ensure that the primary key complies with the following rules:

• The column cannot have null values.

  The specified primary key constraint column during table creation is implicitly converted to not null. Sparse Columns must allow null values. Therefore, Sparse Columns cannot be used as part of a primary key.

• Duplicate values are not allowed.

  If you add a primary key constraint to a column with duplicate values or null values, the database engine returns an error without adding constraints.

You cannot add a primary key constraint that violates the preceding rules.

The database engine automatically creates a unique index to enforce the uniqueness requirements of the primary key constraint. If no clustered index exists in the table or the non-clustered index is not explicitly specified, a unique clustered index is created to enforce the primary key constraint.

If the following conditions exist, the primary key constraint cannot be deleted:

• If the foreign key constraint in another table references the primary key constraint, you must first Delete the foreign key constraint.

• The table contains the primary XML index applied to itself.