Primary key or Unique index?

database, database-design, sql

Solution

What is a unique index?

A unique index on a column is an index on that column that also enforces the constraint that you cannot have two equal values in that column in two different rows. Example:

CREATE TABLE table1 (foo int, bar int);
CREATE UNIQUE INDEX ux_table1_foo ON table1(foo);  -- Create unique index on foo.

INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK
INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails!

Duplicate entry '1' for key 'ux_table1_foo'

The last insert fails because it violates the unique index on column `foo` when it tries to insert the value 1 into this column for a second time.

In MySQL a unique constraint allows multiple NULLs.

It is possible to make a unique index on mutiple columns.

Primary key versus unique index

Things that are the same:

- A primary key implies a unique index.

Things that are different:

- A primary key also implies NOT NULL, but a unique index can be nullable.

- There can be only one primary key, but there can be multiple unique indexes.

- If there is no clustered index defined then the primary key will be the clustered index.

Problem

At work we have a big database with unique indexes instead of primary keys and all works fine. I'm designing new database for a new project and I have a dilemma: In DB theory, primary key is fundamental element, that's OK, but in REAL projects what are advantages and disadvantages of both? What do you use in projects? EDIT: ...and what about primary keys and replication on MS SQL server?

Original source