Regardless of row, is the only column

Question

Is there a way to make the entire row unique regardless of the value in the column?

So, if column a has 1, column b has 2 , then if you insert 2 into column a, insert 1 into column b , an error will be thrown.

 --- ---
| a | b |
 --- ---
| 1 | 2 |
| 2 | 1 | <- Since the above already exists, an error will be thrown when inserting
 --- ---

Answer 1

You can use triggers to enforce execution. But I can't think of a good way to achieve this using UNIQUE KEY constraints.

If you change the way you store the values ​​so that they are in a single column, in multiple rows of the attached table, it will be easier to use UNIQUE KEY to enforce uniqueness.

---

If you want to indicate related items:

CREATE TABLE item_group (
  group_id INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE item_group_members (
  group_id INT NOT NULL,
  member_id INT NOT NULL,
  PRIMARY KEY (group_id, member_id)
);

INSERT INTO item_group_members VALUES (1, 1), (1, 2);

If you need each member to appear in only one group:

ALTER TABLE item_group_members ADD UNIQUE KEY (member_id);

Answer 2

Define 2 virtual generated columns, respectively the minimum value and the maximum value, and set unique constraints 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)
);

ViewDemo.

Or, for MySql 8.0:

CREATE TABLE tablename (
  a INT NOT NULL, 
  b INT NOT NULL,
  UNIQUE ((LEAST(a, b)), (GREATEST(a, b))) -- 不要忘记括号 
);

ViewDemo.