Primary key or Unique index?

Question

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?

Answer 1

If it were up to me...

You need to satisfy the requirements of the database and of your applications.

Adding an auto-incrementing integer or long id column to every table to serve as the primary key takes care of the database requirements.

You would then add at least one other unique index to the table for use by your application. This would be the index on employee_id, or account_id, or customer_id, etc. If possible, this index should not be a composite index.

I would favor indices on several fields individually over composite indices. The database will use the single field indices whenever the where clause includes those fields, but it will only use a composite when you provide the fields in exactly the correct order - meaning it can't use the second field in a composite index unless you provide both the first and second in your where clause.

I am all for using calculated or Function type indices - and would recommend using them over composite indices. It makes it very easy to use the function index by using the same function in your where clause.

This takes care of your application requirements.

It is highly likely that other non-primary indices are actually mappings of that indexes key value to a primary key value, not rowid()'s. This allows for physical sorting operations and deletes to occur without having to recreate these indices.

Answer 2

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.

Answer 3

My understanding is that a primary key and a unique index with a not‑null constraint, are the same (*); and I suppose one choose one or the other depending on what the specification explicitly states or implies (a matter of what you want to express and explicitly enforce). If it requires uniqueness and not‑null, then make it a primary key. If it just happens all parts of a unique index are not‑null without any requirement for that, then just make it a unique index.

The sole remaining difference is, you may have multiple not‑null unique indexes, while you can't have multiple primary keys.

(*) Excepting a practical difference: a primary key can be the default unique key for some operations, like defining a foreign key. Ex. if one define a foreign key referencing a table and does not provide the column name, if the referenced table has a primary key, then the primary key will be the referenced column. Otherwise, the the referenced column will have to be named explicitly.

Others here have mentioned DB replication, but I don't know about it.