Questions tagged [unique-constraint]

Declaration

DDL UNIQUE constraints can be created by adding the UNIQUE keyword to the column declaration on the table:

CREATE TABLE foo (
  bar INT UNIQUE
);

CREATE TABLE foo (
  bar INT PRIMARY KEY -- PRIMARY KEY = "UNIQUE NOT NULL"
); 

…as a table-level constraint:

CREATE TABLE foo (
  bar INT NOT NULL,
  CONSTRAINT foo_ck UNIQUE (Name)
);

…or, with a command after table creation:

-- generate a unique name
ALTER TABLE foo
  UNIQUE (bar);

-- explicitly declare constraint with name `foo_bar`
ALTER TABLE foo
  ADD CONSTRAINT foo_bar
  UNIQUE (bar);

Design considerations

If (a) there is a primary key set up for the table in question, and (b) the whole set of columns (i.e., one or more) configured with this type of constraint is also fixed with NOT NULL constraints, the designer is representing an alternate key:

CREATE TABLE foo (
  foo_id INT      NOT NULL,
  bar    DATETIME NOT NULL,
  CONSTRAINT foo_pk PRIMARY KEY (foo_id),
  CONSTRAINT foo_ak UNIQUE      (bar) -- ALTERNATE KEY
);

If, on the contrary, (1) there is no primary declared for the table under consideration, and (2) all the column (or columns) configured with this type of constraint are also fixed with one (or more) NOT NULL constraint(s), the designer is depicting a candidate key:

CREATE TABLE foo (
  foo_id INT      NOT NULL,
  bar    DATETIME NOT NULL,
  CONSTRAINT foo_ck1 UNIQUE (foo_id), -- CANDIDATE KEY
  CONSTRAINT foo_ck2 UNIQUE (bar)     -- CANDIDATE KEY
);

Manipulation

As for data manipulation operations, the columns constrained as UNIQUE can always be used to uniquely identify a row in the table:

-- returns one row
SELECT * FROM foo WHERE bar = $id;

Supporting indexes

The major SQL database management systems (DBMSs) commonly support UNIQUE constraints with the aid of a well-known structure called unique index, but these instruments should not be confused nor conflated, because a UNIQUE constraint is a construct that is part of the logical level of abstraction of a database, while a unique index is part of the physical (or internal) level.

In this respect, the vendors and/or developers of a DBMS may find it convenient to supply mechanisms that are different from a unique index to support a UNIQUE constraint, thus, as long as the uniqueness declared logically is guaranteed, the usage of unique index is not required. In addition, a unique index can be used in software products that are quite different from a SQL DBMS, where the notion of (i) a logical-level UNIQUE constraint, and (ii) the associated ideas of ALTERNATE and CANDIDATE KEYs, are (iii) completely irrelevant.

If your UNIQUE constraint declaration is, precisely, physically implemented with an index, this means you can seek to it, and the DBMS query planner will know that the clause returns 0, or 1 row. Some DBMSs allow you to specify that an index is unique on creation,

-- Valid with PostgreSQL,
CREATE UNIQUE INDEX ON foo (bar);

Terminology and keywords

Sometimes, the expression “unique key” is used when referring to a UNIQUE constraint —e.g., in the official documentation of several SQL platforms—.

Also, a database designer can even set up a UNIQUE constraint by virtue of the UNIQUE KEY combination of keywords in MySQL.

In this regard, both (a) the “unique key” term and (b) the UNIQUE + KEY combination are redundant, since:

• a KEY constraint —be it PRIMARY, ALTERNATE or CANDIDATE— is, by its very nature, meant to enforce uniqueness of the value (or values) contained in the column (or columns) of the table under consideration; and

• one can set up a UNIQUE constraint without adding the “KEY” part.

See also

RDBMS-specific documentation:

• PostgreSQL
• Sybase/SAP ASE
• SQL Server
• Firebird
• DB2
• MySQL