SQL Database Design » 10. Constraints » 10.3 Unique Constraints

10.3 Unique Constraints

Unique constraints (sometimes falsely called unique keys) are one of the most neglected features in database design. Unique contraints mark alternative keys, also called candidate or secondary keys. They denote columns which could also have been used as primary keys, but just were not chosen for some reason. They are much like primary keys, but they can become NULL (default), if you do not declare them to be explicitly NOT NULL. You may declare more than one unique constraint per table and they may span several columns. There is no problem with declaring foreign keys to alternative keys.

As with primary keys, the unique constraint usually instructs the DBMS to automatically create an index for the columns involved. But there are practical situations, which are not just about performance. Take the following example of modeling international phone numbers:

CREATE TABLE Countries
(
  iso_code CHAR(2) PRIMARY KEY,
  ...
  dial_code VARCHAR(3) NOT NULL UNIQUE,
  ...
)

CREATE TABLE PhoneNumbers
(
  intl_code VARCHAR(3) NOT NULL REFERENCES Countries (dial_code),
  area_code VARCHAR(5) NOT NULL,
  subscriber_nbr VARCHAR(10) NOT NULL,
  PRIMARY KEY (intl_code, area_code, subscriber_nbr),
)

Note, in reality, some countries share the same dial code, e.g. the USA, Canada, and some Caribbean countries share the "001". For this example we pretend every country had its own unique dialing code. In this constellation, a non-primary-key column is referenced by another table. Referential integrity can still be ensured if the referenced attribute is marked as NOT NULL UNIQUE.

The nice thing to note here is, that there is no need to join with the IsoCountries table to get the complete phone number data. While this is only a small use case, it demonstrates, that alternative key can have uses, beside the automatic index.

However, the main benefit of unique constraints comes with the use of artificial (single-column) keys. See section 13.1.5 Using Identity Keys for an example why (multi-colum) unique constraints are probably one of the most neglected features in SQL.

References for Unique Constraints:

Last updated: 2010-08-04