Unique combination key MySQL

Question

Lets say I have a table with 3 fields:

• id1
• id2
• mapTypeId

What I want is a primary/unique key that is any combination of id1 and id2. For example if I have this record already in the database:

• 1, 2, 3

I should not be able to insert a record like:

• 2, 1, 3

Right now I have id1 and id2 as my composite primary key however I am able to insert the data above, is there anyway to prevent this on the database level?

Answer 1

Here is before trigger you can use to guarantee that id1 and id2 are inserted in min/max order

DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;

Here is some Sample Data:

DROP DATABASE IF EXISTS ryanzec;
CREATE DATABASE ryanzec;
USE ryanzec
CREATE TABLE mytable
(
    id1 INT NOT NULL,
    id2 INT NOT NULL,
    mapTypeId INT NOT NULL,
    primary key (id1,id2)
);
DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;
INSERT INTO mytable VALUES (1,2,3);
INSERT INTO mytable VALUES (2,1,3);
INSERT INTO mytable VALUES (5,4,6);
INSERT INTO mytable VALUES (4,5,6);
SELECT * FROM mytable;

Here it is the Sample Data Loaded

mysql> DROP DATABASE IF EXISTS ryanzec;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE ryanzec;
Query OK, 1 row affected (0.00 sec)

mysql> USE ryanzec
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id1 INT NOT NULL,
    ->     id2 INT NOT NULL,
    ->     mapTypeId INT NOT NULL,
    ->     primary key (id1,id2)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE sum INT;
    ->     SET sum = NEW.id1 + NEW.id2;
    ->     SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    ->     SET NEW.id2 = sum - NEW.id1;
    -> END $$
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;

Watch What Happens When I Insert the Rows:

mysql> INSERT INTO mytable VALUES (1,2,3);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO mytable VALUES (2,1,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO mytable VALUES (5,4,6);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO mytable VALUES (4,5,6);
ERROR 1062 (23000): Duplicate entry '4-5' for key 'PRIMARY'
mysql> SELECT * FROM mytable;
+-----+-----+-----------+
| id1 | id2 | mapTypeId |
+-----+-----+-----------+
|   1 |   2 |         3 |
|   4 |   5 |         6 |
+-----+-----+-----------+
2 rows in set (0.00 sec)

mysql>

Now, no matter what order id1 and id2 are entered, id1 will always be less than or equal to id2

This may not be contextually what you want because id1 and id2 are unique identifiers.

Answer 2

Yes you can but not with an index. You have to remember that the values [2,1,3] are a unique value even if the value [1,2,3] already exists. However you have 2 options available to you:

• You could enforce this logic using a trigger to check that the value of id1 does not exist in id2 and vice-versa.

• You could perform the check inside the stored procedure or business logic that inserts the data.(Recommended)

I hope this helps.

EDIT: As per the comment below MySQL does not support CHECK constraints, removed suggestion in the list above.

Answer 3

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), through the VIRTUAL columns feature, this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne INT AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo INT AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.

---

This is another idea (untested, not really sure if it will work), to have one less column and no persistent columns:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, diff INT UNSIGNED AS ( id2 - id1 ) VIRTUAL 
         --- a computed column that will hopefully throw an error if id1 > id2
, PRIMARY KEY (id1, id2)
, ...                                   --- other FOREIGN KEY constraints, etc 
) ;