Creating and Modifying PRIMARY KEY Constraints

A single PRIMARY KEY constraint can be:

• Created when the table is created, as part of the table definition.
• Added to an existing table, provided that no other PRIMARY KEY constraint already exists (a table can have only one PRIMARY KEY constraint).
• Modified or deleted, if it already exists. For example, you may want the PRIMARY KEY constraint of the table to reference other columns, or you may want to change the column order, index name, clustered option, or fill factor of the PRIMARY KEY constraint. It is not possible to change the length of a column defined with a PRIMARY KEY constraint.

  ---

  Note To modify a PRIMARY KEY constraint using Transact-SQL or SQL-DMO, you must first delete the existing PRIMARY KEY constraint and then re-create it with the new definition.

  ---

When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

• No null values
• No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.

SQL Server automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table, or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

Important A PRIMARY KEY constraint cannot be deleted if it is being referenced by a FOREIGN KEY constraint in another table; the FOREIGN KEY constraint must be deleted first.

To create a PRIMARY KEY constraint when creating a table

To create or delete a PRIMARY KEY constraint on an existing table

To modify a PRIMARY KEY constraint

See Also

Primary Key Constraints