Thursday, July 21, 2011

Difference between UNIQUE constraint and PRIMARY KEY constraint

You can use a UNIQUE constraint if you want to give uniqueness to a column which is not primary key.

You can have more than one UNIQUE constraints in a table, where as there can be only one PRIMARY KEY in a table.

To modify a PRIMARY KEY constraint, you need to drop it and re-create.

Also, PRIMARY KEY column should be NOT NULL and UNIQUE constraints can be null.

If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY constraint, the default value will be CLUSTERED.

If no clustered indexes are specified for UNIQUE constraint, NONCLUSTERED is set as default value.

Since a table can have only one clustered key, if you define a PRIMARY constraint and if it is not NONCLUSTERED, you can't specify a UNIQUE constraint as CLUSTERED.

No comments:

Post a Comment