Poon's Blog

the difference between primary key and unique constraint
Primary Keys
A primary key is a special constraint that is used to ensure that values in a column (or set of columns) are unique and * never change *, in other words, a column (or columns) in a table whose values uniquely identify * each row * in the table. This facilitates the direct manipulation of and interaction with individual rows. Without primary keys, it would be very difficult to safely UPDATE or DELETE specific rows without affecting any others.

Any column in a table can be established as the primary key, as long as it meets the following conditions:

No two rows may have the same primary key value.

Every row must have a primary key value. (Columns must not allow NULL values.)

The column containing primary key values can never be modified or updated.

Primary key values can never be reused. If a row is deleted from the table, its primary key must not be assigned to any new rows.

One way to define primary keys is to create them.

Unique Constraints

Unique constraints are used to ensure that all data in a column (or set of columns) is unique. They are similar to primary keys, but there are some important distinctions:

A table can contain multiple unique constraints, but only one primary key is allowed per table.

Unique constraint columns can contain NULL values.

Unique constraint columns can be modified or updated.

Unique constraint column values can be reused.

Unlike primary keys, unique constraints cannot be used to define foreign keys.