question

Matt Whitfield avatar image
Matt Whitfield asked

What is the difference between PRIMARY KEY and UNIQUE constraints?

Seeder question: I have heard that PRIMARY KEY and UNIQUE constraints can be used interchangeably - is this true? What is the difference between the two?

primary-keyconstraintunique
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Raj More avatar image
Raj More answered

Here are the various points I know of:

  • You can only have one primary key per table, but multiple unique constraints (249, I believe)

  • When you create a unique constraint, SQL Server creates a Unique index. This is Non-Clustered by default, but it can be changed. When you create a primary key, a Unique Constraint Primary Key is created - you have the option to set IGNORE_DUP_KEY. The PK is Clustered by default - this can be changed.

  • A PK can not have a NULL value - if you try to make a NULLABlE column as your PK, SQL Server will change it to NOT NULL first, and then create the PK. A Unique constraint can have one NULL value.

They can be used interchangeably up to a certain degree - it depends on how you intend to use it.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jay Bonk avatar image
Jay Bonk answered

A primary key, as well as a unique constraint will uniquely identify a row in a given table. Each table, can have a single primary key, and multiple unique constraints. As Raj mentioned, a Primary Key cannot have a NULL value and a Unique Constraint can have a single NULL Value (Unless using a filtered index).

Both a Primary Key and a Unique Constraint, are implemented as indexes on the table. And, while SQL Server will by default create the PK as the clustered index, it isn't a requirement that the clustered index be the PK on the table, it is possible to create another index as the clustered index.

Additionally, you can create a UNIQUE CLUSTERED INDEX on the table, that while not technically a unique constraint can be the clustered index and not the PK.

Whereas, a UNIQUE Constraint is implemented as an Index, a Unique Index is not implemented as a constraint.

Often, unique constraints are referred to as ALTERNATE KEYS.

One other item of interest, is that either a PK or a Unique Constraint can be used as a Foreign Key....

CREATE TABLE TEST
(
    PK_COL  INT NOT NULL
    ,AK_COL INT NOT NULL
    ,UC_COL INT NOT NULL
)
;

ALTER TABLE TEST
    ADD CONSTRAINT PK_TEST PRIMARY KEY NONCLUSTERED (PK_COL)
;

CREATE UNIQUE CLUSTERED INDEX CUI_TEST ON TEST
(
    AK_COL
);

ALTER TABLE TEST 
    ADD CONSTRAINT UC_TEST UNIQUE (UC_COL)
;

EXEC sp_helpindex TEST
;

EXEC sp_helpconstraint TEST
;

DROP TABLE TEST
;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Idstam avatar image
Idstam answered

You can only have one primary key on a table but using unique constraints you can enforce uniqueness on more columns (or combinations of columns)

A standard setup for a user table is to have the primary key set to a user id or user name and have a unique contraint on their email address.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steve Jones - Editor avatar image
Steve Jones - Editor answered

I think Raj has a complete answer, but I'll add one thing. A Primary Key is a unique constraint, but a unique constraint is not a primary key. A unique constraint is a superset of primary keys, or PKs a subset of the unique constraints.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

One difference that hasn't been mentioned yet is that a PRIMARY KEY constraint will be the default target for a FOREIGN KEY constraint referencing that table if you create the FOREIGN KEY without specifying a column list. Otherwise PRIMARY KEY achieves the same thing as a UNIQUE constraint on NOT NULL columns.

It is a very widely observed convention of SQL database design that every table should have a PRIMARY KEY constraint. This is of little practical consequence however and it would be quite possible (and very possibly desirable) to do without PRIMARY KEY constraints altogether.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Solo avatar image
Solo answered

A primary key is a unique field on a table but it is special in the sense that the table considers that row as its key. This means that other tables can use this field to create foreign key relationships to themselves.

A unique constraint simply means that a particular field must be unique.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.