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

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!

Answer 2

The choice of when to use a surrogate primary key as opposed to a natural key is tricky. Answers such as, always or never, are rarely useful. I find that it depends on the situation.

As an example, I have the following tables:

CREATE TABLE toll_booths (
    id            INTEGER       NOT NULL PRIMARY KEY,
    name          VARCHAR(255)  NOT NULL,
    ...
    UNIQUE(name)
)

CREATE TABLE cars (
    vin           VARCHAR(17)   NOT NULL PRIMARY KEY,
    license_plate VARCHAR(10)   NOT NULL,
    ...
    UNIQUE(license_plate)
)

CREATE TABLE drive_through (
    id            INTEGER       NOT NULL PRIMARY KEY,
    toll_booth_id INTEGER       NOT NULL REFERENCES toll_booths(id),
    vin           VARCHAR(17)   NOT NULL REFERENCES cars(vin),
    at            TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    amount        NUMERIC(10,4) NOT NULL,
    ...
    UNIQUE(toll_booth_id, vin)
)

We have two entity tables (toll_booths and cars) and a transaction table (drive_through). The toll_booth table uses a surrogate key because it has no natural attribute that is not guaranteed to change (the name can easily be changed). The cars table uses a natural primary key because it has a non-changing unique identifier (vin). The drive_through transaction table uses a surrogate key for easy identification, but also has a unique constraint on the attributes that are guaranteed to be unique at the time the record is inserted.

http://database-programmer.blogspot.com has some great articles on this particular subject.

Answer 3

There are some disadvantages of CLUSTERED INDEXES vs UNIQUE INDEXES.

As already stated, a CLUSTERED INDEX physically orders the data in the table.

This mean that when you have a lot if inserts or deletes on a table containing a clustered index, everytime (well, almost, depending on your fill factor) you change the data, the physical table needs to be updated to stay sorted.

In relative small tables, this is fine, but when getting to tables that have GB's worth of data, and insertrs/deletes affect the sorting, you will run into problems.

Answer 4

Unique Index can have one NULL value. It creates NON-CLUSTERED INDEX. Primary Key cannot contain NULL value. It creates CLUSTERED INDEX.

Answer 5

In MSSQL, Primary keys should be monotonically increasing for best performance on the clustered index. Therefore an integer with identity insert is better than any natural key that might not be monotonically increasing.

Answer 6

There is no such thing as a primary key in relational data theory, so your question has to be answered on the practical level.

Unique indexes are not part of the SQL standard. The particular implementation of a DBMS will determine what are the consequences of declaring a unique index.

In Oracle, declaring a primary key will result in a unique index being created on your behalf, so the question is almost moot. I can't tell you about other DBMS products.

I favor declaring a primary key. This has the effect of forbidding NULLs in the key column(s) as well as forbidding duplicates. I also favor declaring REFERENCES constraints to enforce entity integrity. In many cases, declaring an index on the coulmn(s) of a foreign key will speed up joins. This kind of index should in general not be unique.