I created the following table called books
:
CREATE TABLE IF NOT EXISTS `books` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I created another tablet called compare to compare any 2 books from the books
table:
CREATE TABLE IF NOT EXISTS `compare` (
`id_1` BIGINT UNSIGNED NOT NULL,
`id_2` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (`id_1`,`id_2`),
FOREIGN KEY (`id_1`) REFERENCES books(`id`),
FOREIGN KEY (`id_2`) REFERENCES books(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The previous works as expected but I need to force MySQL to allow ONLY unique combination of values in the compare
table.
For example if I have the following row in compare
table:
id_1 | id_2
------------
1 | 2
I want to force MySQL to NOT allow me to insert the following row:
id_1 | id_2
------------
2 | 1
So I want MySQL to allow only either 1,2
or 2,1
NOT both.
I am using 10.2.14-MariaDB – MariaDB Server
Best Answer
Since you use MariaDB 10.2, you can add a
CHECK
constraint, enforcing that the first id is less than the second (id_1 < id_2
).CHECK
constraints were added in version 10.2.1.This has the limitation that you'll have to provide the ids for the
INSERT
statements in correct order (you won't be able to insert the(3,2)
combination, even if(2,3)
is not there):Tested at dbfiddle.uk.
If you don't want to have to hassle with your
INSERT
statements and procedures, you can use twoVIRTUAL
(orPERSISTENT
) columns and aUNIQUE
constraint.Virtual columns are available in MariaDB since version 5.2 and several limitations were lifted in 10.2.1:
Tested at dbfiddle.uk, too: fiddle-2.