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™ 2000 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 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
Transact-SQLTo create or delete a PRIMARY KEY constraint on an existing table
To modify a PRIMARY KEY constraint