Creating and Modifying PRIMARY KEY Constraints
You can create a single PRIMARY KEY constraint as part of the table definition when you create a table is created. If a table already exists, you can add PRIMARY KEY constraint, provided that no other PRIMARY KEY constraint already exists. A table can have only one PRIMARY KEY constraint.
If a PRIMARY KEY constraint already exists, you can modify or delete it. 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. However, you cannot change the length of a column defined with a PRIMARY KEY constraint.
Note
To modify a PRIMARY KEY constraint, 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, the SQL Server 2005 Database Engine examines the existing column data and metadata to make sure that the following rules for primary keys:
- The columns cannot allow for null values.
Columns of PRIMARY KEY constraints that are specified when a table is created are implicitly converted to NOT NULL. - There can be no duplicate values.
If a PRIMARY KEY constraint is added to a column that has duplicate values or allows for null values, the Database Engine returns an error and does not add the constraint.
You cannot add a PRIMARY KEY constraint that violates these rules.
The Database Engine 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.
A PRIMARY KEY constraint cannot be deleted if the following exist:
- If it is referenced by a FOREIGN KEY constraint in another table; the FOREIGN KEY constraint must be deleted first.
- The table has a PRIMARY XML index applied on it.
To create a PRIMARY KEY constraint when you create a table
To create or delete a PRIMARY KEY constraint on an existing table
How to: Create Primary Keys (Visual Database Tools)
How to: Delete Primary Keys (Visual Database Tools)
To obtain information about PRIMARY KEY constraints
sys.key_constraints (Transact-SQL)