Primary key or Unique index?

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.

You can see it like this:

A Primary Key IS Unique

A Unique value doesn't have to be the Representaion of the Element

Meaning?; Well a primary key is used to identify the element, if you have a "Person" you would like to have a Personal Identification Number ( SSN or such ) which is Primary to your Person.

On the other hand, the person might have an e-mail which is unique, but doensn't identify the person.

I always have Primary Keys, even in relationship tables ( the mid-table / connection table ) I might have them. Why? Well I like to follow a standard when coding, if the "Person" has an identifier, the Car has an identifier, well, then the Person -> Car should have an identifier as well!

Foreign keys work with unique constraints as well as primary keys. From Books Online:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table

For transactional replication, you need the primary key. From Books Online:

Tables published for transactional replication must have a primary key. If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.

Both answers are for SQL Server 2005.