UNIQUE KEY on multiple columns in the forward and reverse

Hello!

I am wondering whether you can make a key that would eliminate the possibility of duplicates. That is, let's say I have a SQL code:

CREATE TABLE IF NOT EXISTS `friends` (
	`initiator_id` INT UNSIGNED NOT NULL,
	`friend_id` INT UNSIGNED NOT NULL,
	`friendship_status` BOOL DEFAULT FALSE,
	UNIQUE KEY(`initiator_id`, `friend_id`),
	INDEX (`initiator_id`, `friend_id`),
	FOREIGN KEY (`initiator_id`) REFERENCES `users`(`user_id`)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	FOREIGN KEY (`friend_id`) REFERENCES `users`(`user_id`)
		ON UPDATE CASCADE
		ON DELETE CASCADE
) ENGINE=InnoDB;

Code (markup):

Accordingly, I add the following entries:
insert into `friends` (`initiator_id`, `friend_id`) values (1,2);

when trying to add
insert into `friends` (`initiator_id`, `friend_id`) values (1,2);
Again, I have a bug (as it should be and that's good).

But I still need some way to achieve what would you try to add
insert into `friends` (`initiator_id`, `friend_id`) values (2,1);
I also have an error ...

Can it somehow implement the SQL in the description of a table or implement this logic in the script or create a stored procedure that I will pull every time you try to add a record to the table?

 

There is no direct solution to this problem as such.

Following are work around you can do.

Query each time for reverse pair of A => B to know if B => A is available.

OR

Create a third column, which stores ascending sorted value with a predefined separator having unique index on it.
e.g. If 1 => 2, third column will have 1#$#2, consider #$# is a separator.
Now when 2 tries inviting 1, after sorting and joining them with separator, it will become 1#$#2 again which will generate error when insert will be tried.