What is the difference between PRIMARY KEY and UNIQUE CLUSTERED index?

• mweaver

  Old Hand

  Points: 336

  More actions

  June 18, 2004 at 8:56 am

  #108396

  Is there any fundamental difference between a PRIMARY KEY and a UNIQUE CLUSTERED INDEX?

  In MSDN (under CREATE TABLE), it says:

  "PRIMARY KEY

  Is a constraint that enforces entity integrity for a given column or columns through a unique index. Only one PRIMARY KEY constraint can be created per table.

  UNIQUE

  Is a constraint that provides entity integrity for a given column or columns through a unique index. A table can have multiple UNIQUE constraints.

  CLUSTERED | NONCLUSTERED

  Are keywords to indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED and UNIQUE constraints default to NONCLUSTERED.

  You can specify CLUSTERED for only one constraint in a CREATE TABLE statement. If you specify CLUSTERED for a UNIQUE constraint and also specify a PRIMARY KEY constraint, the PRIMARY KEY defaults to NONCLUSTERED."

  So, the only thing that I have been able to observe is that the PRIMARY KEY forces all of the columns in the key to be NOT NULL, whereas UNIQUE CLUSTERED allows columns to be NULL.  Does anyone know of any other differences - or is PRIMARY KEY simply an alias for a UNIQUE CLUSTERED index that forces required columns?

• David.Poole

  SSC Guru

  Points: 76001

  More actions

  June 18, 2004 at 12:25 pm

  #511100

  A primary key defaults to a clustered index, but you can choose explicitly not to cluster it.

  I'm pretty sure that a a primary key is needed for certain types of replication.

  I'm also pretty sure that a primary key is necessary when using full-text indices.

  Good practice is for the primary key not to be updated, ditto a clustered index, if it is not your primary key.

  A unique constraint/index can contain a single null value, where as a primary key cannot be null.

  LinkedIn Profile