UNIQUE

Enforces uniqueness of each value of a row, except for NULL which may appear several times.

This can only be maintained efficiently if an index is created, so UNIQUE also creates an index, thus implying INDEX:

CREATE TABLE t (a INT UNIQUE, b INT);
INSERT INTO t VALUES (0, 0), (1, 0);
INSERT INTO t VALUES (0, 1);
SELECT * FROM t;
DROP TABLE t;

Output:

ERROR 1062 (23000): Duplicate entry '0' for key 'a'
0    0
1    0

Error can be ignored with INSERT IGNORE.

Unique pairs:

CREATE TABLE t (
    a INT,
    b INT,
    UNIQUE INDEX (a, b)
);
INSERT INTO t VALUES (0, 0), (0, 1), (1, 0);
INSERT INTO t VALUES (0, 1);
SELECT * FROM t;
DROP TABLE t;
Comments
comments powered by Disqus