Compulsory composite primary order

Question

Let's say I have the following table

item_a_id, item_b_id, value 

Where item_a_id and item_b_id are the composite primary key. In my example, a, b and b, a are equivalent. So I want item_a_id <item_b_id. Obviously, application logic will provide this, but is there a way to provide a database too?

Answer 1

In the fairly current version of MySql, you can use triggers to emulate a validation constraint that creates the desired behavior.

Answer 2

In your case, you can use a trigger to check the values ​​before inserting / updating and replacing it, to ensure that item_a_id will always be less than item_b_id .

Assuming the table name is item_links , you can try the following:

 DELIMITER | CREATE TRIGGER ensure_a_b_before_insert BEFORE INSERT ON item_links FOR EACH ROW BEGIN IF NEW.item_a_id > NEW.item_b_id THEN SET @tmp = NEW.item_b_id; SET NEW.item_b_id = NEW.item_a_id; SET NEW.item_a_id = @tmp; END IF; END; | CREATE TRIGGER ensure_a_b_before_update BEFORE UPDATE ON item_links FOR EACH ROW BEGIN IF NEW.item_a_id > NEW.item_b_id THEN SET @tmp = NEW.item_b_id; SET NEW.item_b_id = NEW.item_a_id; SET NEW.item_a_id = @tmp; END IF; END; | DELIMITER ; 

Here is what I got when testing the insert:

 mysql> INSERT INTO `item_links` (`item_a_id`, `item_b_id`, `value`) -> VALUES ('1', '2', 'a') -> , ('3', '2', 'b') -> , ('4', '1', 'c'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM `item_links`; +-----------+-----------+-------+ | item_a_id | item_b_id | value | +-----------+-----------+-------+ | 1 | 2 | a | | 2 | 3 | b | | 1 | 4 | c | +-----------+-----------+-------+ 3 rows in set (0.00 sec) 

The update also works:

 mysql> UPDATE `item_links` -> SET `item_a_id` = 100, `item_b_id` = 20 -> WHERE `item_a_id` = 1 AND `item_b_id` = 2; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM `item_links`; +-----------+-----------+-------+ | item_a_id | item_b_id | value | +-----------+-----------+-------+ | 20 | 100 | a | | 2 | 3 | b | | 1 | 4 | c | +-----------+-----------+-------+ 3 rows in set (0.00 sec)