Primary key or Unique index?

Question

Hello,

in work we have big database with unique indexes instead of primary keys and all works fine.

I`m designing new database for new project and I have a dilema:

in DB theory, primary key is fundamental element,it`s OK... but in REAL projects what are adventages and disadventages of both?

what do you use in projects?

EDIT:...and what about primary keys and replication on mssql 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 elemnt, 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 identifiery, the Car has an identifier, well, then the Person -> Car should have an identifier aswell!

Answer 2

If something is a primary key, depending on your DB engine, the entire table gets sorted by the primary key. This means that lookups are much faster on the primary key because it doesn't have to do any dereferencing as it has to do with any other kind of index. Besides that, it's just theory.

Answer 3

In addition to what the other answers have said, some databases and systems may require a primary to be present. One situation comes to mind; when using enterprise replication with Informix a PK must be present for a table to participate in replication.

Answer 4

As long as you do not allow NULL for a value, they should be handled the same, but the value NULL is handled differently on databases(AFAIK MS-SQL do not allow more than one(1) NULL value, mySQL and Oracle allow this, if a column is UNIQUE) So you must define this column NOT NULL UNIQUE INDEX

Answer 5

There are no disadvantages of primary keys.

To add just some information to @MrWiggles and @Peter Parker answers, when table doesn't have primary key for example you won't be able to edit data in some applications (they will end up saying sth like cannot edit / delete data without primary key). Postgresql allows multiple NULL values to be in UNIQUE column, PRIMARY KEY doesn't allow NULLs. Also some ORM that generate code may have some problems with tables without primary keys.

UPDATE:

As far as I know it is not possible to replicate tables without primary keys in MSSQL, at least without problems (details).

Answer 6

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 7

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.

Answer 8

OK thanks guys I try to design my database with primary keys,

but another related question

What about bussiness specific primary keys?

I read somewhere, that primary keys should be unique numbers not related with bossiness model

Answer 9

I almost never create a table without a numeric primary key. If there is also a natural key that should be unique, I also put a unique index on it. Joins are faster on integers than multicolumn natural keys, data only needs to change in one place (natural keys tend to need to be updated which is a bad thing when it is in primary key - foreign key relationships). If you are going to need replication use a GUID instead of an integer, but for the most part I prefer a key that is user readable especially if they need to see it to distinguish between John Smith and John Smith.

The few times I don't create a surrogate key are when I have a joining table that is involved in a many-to-many relationship. In this case I declare both fields as the primary key.

Answer 10

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 11

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.