Question 177571 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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
+---+---+

Based on Justinas php answer I created a mysql trigger on the table:

BEGIN
   DECLARE group_md5 CHAR(32);

   SELECT MD5(GROUP_CONCAT(plant_id SEPARATOR ',')) INTO group_md5 FROM (
      SELECT NEW.plant_a AS plant_id
      UNION 
      SELECT NEW.plant_b AS plant_id
      ORDER BY plant_id
   ) AS plant_list;
   
   SET NEW.hash = group_md5;
END

 CREATE TABLE `compliments` (
    `plant_a` INT(10) NOT NULL,
    `plant_b` INT(10) NOT NULL,
    `hash` CHAR(32) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    UNIQUE INDEX `hash` (`hash`) USING BTREE
)
Tags:

3

Answers


  1. 0

    You could enforce it with a trigger. But I can’t think of a good way to do this with a UNIQUE KEY constraint.

    It would be easier if you change the way you store values, so they are in a single column, on multiple rows of an additional table. Then you could easy use UNIQUE KEY to enforce uniqueness.

    If you’re trying to represent 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);
    Login or Signup to reply.
  2. 0

    Have third column hash VARCHAR NOT NULL UNIQUE, where you would place sorted hash.
    Sort it to always get same hash for values, no matter of their order: 1, 2, 2, 3 will have same hash as 3, 2, 1, 2

    E.g. in PHP:

    sort($columns);
$hash = md5(implode('|', $columns));

$columns['hash'] = $hash;

$sql = "INSERT INTO my_tbl VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute($columns);
    Login or Signup to reply.
  3. 0

    When inserting data check for duplicates using before insert triggers like so:

    IF (EXISTS(SELECT 1 FROM t WHERE (LEAST(a), GREATEST(b)) = (LEAST(NEW.a), GREATEST(NEW.b)))) THEN
    SIGNAL ...

    A simpler solution is to add CHECK constraint along with UNIQUE on your table:

    UNIQUE (a, b)
CHECK (a < b)

    Now, you must store data so that a < b. If you try to insert 2, 1 the check fails. If you insert 1, 2 the unique constraint fails.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search