clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 648391 fiddles created (15115 in the last week).

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;
 hidden batch(es)


INSERT INTO books (id, name) values (1, 'A'), (2, 'B'), (3, 'C') ;
 hidden batch(es)


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;
 hidden batch(es)


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);
 hidden batch(es)


INSERT INTO compare (id_1, id_2) values (1, 2) ;
 hidden batch(es)


select * from compare ;
id_1 id_2 small_id big_id
1 2 1 2
 hidden batch(es)


INSERT INTO compare (id_1, id_2) values (2, 1) ;
Duplicate entry '1-2' for key 'ids_unique_combination_uq'
 hidden batch(es)


INSERT INTO compare (id_1, id_2) values (2, 3), (3,2) ;
Duplicate entry '2-3' for key 'ids_unique_combination_uq'
 hidden batch(es)


INSERT INTO compare (id_1, id_2) values (3,2) ;
 hidden batch(es)


select * from compare ;
id_1 id_2 small_id big_id
1 2 1 2
3 2 2 3
 hidden batch(es)