Как лучше всего обеспечить уникальность ФК во всех перестановках?

Начиная с MySql 8.0.16, вы можете добавить ограничение CHECK , которое фактически что-то делает.

(Между MySql 5.7 и 8.0.16 вы можете добавить один, но он будет просто проигнорирован)

Для MariaDb рабочее ограничение CHECK было реализовано начиная с версии 10.2.1. Ссылка

Так что, если ваша версия поддерживает это, то это может быть что-то вроде этого примера:

create table Friend (
  id int primary key auto_increment, 
  name varchar(30) not null
);

insert into Friend (name) values
('John Doe'), ('Jane Shepard')

create table Friendship (
  id int primary key auto_increment,
  fk_friend_one_id int not null,
  fk_friend_two_id int not null,
  CONSTRAINT chk_friend2_gt_friend1 check (fk_friend_two_id > fk_friend_one_id),
  CONSTRAINT fk_friend_one_id foreign key (fk_friend_one_id) references Friend(id),
  CONSTRAINT fk_friend_two_id foreign key (fk_friend_two_id) references Friend(id),
  CONSTRAINT idx_friends unique index (fk_friend_one_id, fk_friend_two_id)
);

insert into Friendship (fk_friend_one_id, fk_friend_two_id) value (2, 1);

[112 ]

insert into Friendship (fk_friend_one_id, fk_friend_two_id) value (1, 1);

CONSTRAINT `chk_friend2_gt_friend1` failed for `mydb`.`Friendship`

insert into Friendship (fk_friend_one_id, fk_friend_two_id) value (1, 2);

select * from Friendship order by id;

id | fk_friend_one_id | fk_friend_two_id
-: | ---------------: | ---------------:
 1 |                1 |                2

db <> скрипка здесь

(Другие Ответы работают слишком усердно. Все, что нужно, это UNIQUE и некоторый простой код SQL.)

Если у вас есть значения $ x и $ y для вставки в таблицу, тогда Обязательно поместите их в каноническом порядке:

INSERT INTO tbl (a, b)
    VALUES ( LEAST($x, $y),
             GREATEST($x, $y) )

И имейте

UNIQUE(a, b)   -- or, better yet, PRIMARY KEY(a,b)

Обратите внимание, что значения (1,2) или (2,1), единственные будет вставлена ​​строка (1,2)

Что нужно сделать, если вы попытаетесь вставить оба? Один войдет как (1,2); другой получит ошибку на INSERT. Вы можете игнорировать ошибку через INSERT IGNORE. Если есть другие столбцы, требующие настройки, рассмотрите возможность использования INSERT .. ON DUPLICATE KEY UPDATE ..

Это может быть достигнуто с помощью хранимых процедур и триггеров . Вы можете попробовать что-то вроде этого:

DELIMITER $

CREATE PROCEDURE `friendship_check`(IN fk_friend_one_id INT, IN fk_friend_two_id INT)
BEGIN
    IF fk_friend_one_id < fk_friend_two_id THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'check constraint on friendship failed';
    END IF;
END$
DELIMITER ;


-- before insert trigger
DELIMITER $
CREATE TRIGGER `friendship_before_insert` BEFORE INSERT ON `Friendship`
FOR EACH ROW
BEGIN
    CALL friendship_check(new.fk_friend_one_id,new.fk_friend_two_id);
END$
DELIMITER ;