Export (0) Print
Expand All

Creating and Modifying PRIMARY KEY Constraints

You can create a single PRIMARY KEY constraint as part of the table definition when 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.

NoteNote

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 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. A sparse column cannot be used as a part of a primary key because sparse columns must allow null values.

  • 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

To obtain information about PRIMARY KEY constraints

Community Additions

ADD
Show:
© 2014 Microsoft