Search code examples
sqlmysqlcreate-tableunique-constraint

Unique row no matter the column

Is there a way to make an entire row unique no matter what value the column is in?

So, if 1 is in col a, and 2 is in col b, then if you were to insert 2 into col a and 1 into col b an error would be thrown.

+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 2 | 1 | <- Would throw error on insert since this already exists above
+---+---+
Solution

  • Define 2 virtual generated columns with the least and the greatest of the 2 values and set a unique constraint on them:

    CREATE TABLE tablename (
  a INT NOT NULL, 
  b INT NOT NULL,
  x INT GENERATED ALWAYS AS (LEAST(a, b)),
  y INT GENERATED ALWAYS AS (GREATEST(a, b)),
  UNIQUE (x, y)
);

    See the demo.

    Or, for MySql 8.0+:

    CREATE TABLE tablename (
  a INT NOT NULL, 
  b INT NOT NULL,
  UNIQUE ((LEAST(a, b)), (GREATEST(a, b))) -- don't miss the parentheses 
);

    See the demo.