Allow only unique combination of values for a composite primary key

Question

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

Answer 1

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):

ALTER TABLE compare
  ADD CONSTRAINT ids_unique_combination_chk
    CHECK (id_1 < id_2) ;

Tested at dbfiddle.uk.

---

If you don't want to have to hassle with your INSERT statements and procedures, you can use two VIRTUAL (or PERSISTENT) columns and a UNIQUE constraint.

Virtual columns are available in MariaDB since version 5.2 and several limitations were lifted in 10.2.1:

ALTER TABLE compare
  ADD small_id BIGINT AS (LEAST(id_1, id_2)) VIRTUAL,
  ADD big_id   BIGINT AS (GREATEST(id_1, id_2)) VIRTUAL,
  ADD CONSTRAINT ids_unique_combination_uq
    UNIQUE (small_id, big_id);

Tested at dbfiddle.uk, too: fiddle-2.