Блог

Я пытаюсь установить отношение «многие ко многим» в таблице: один keyword может быть связан с другим keyword , и когда это отношение добавляется, также должно быть установлено другое направление.

Бежим дальше 10.5.9-MariaDB .

 CREATE TABLE `related_keywords` (
  `relation_id` int(12) NOT NULL AUTO_INCREMENT COMMENT 'Laravel/Eloquent pk crutch',
  `keyword_1` bigint(20) unsigned NOT NULL,
  `keyword_2` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`relation_id`),
  UNIQUE KEY `rel_kw_left_to_right` (`keyword_1`,`keyword_2`),
  UNIQUE KEY `rel_kw_right_to_left` (`keyword_2`,`keyword_1`),
  CONSTRAINT `fk_rel_kw_1_exists` FOREIGN KEY (`keyword_1`) REFERENCES `keywords` (`keyword_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rel_kw_2_exists` FOREIGN KEY (`keyword_2`) REFERENCES `keywords` (`keyword_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_related_kw_lr` FOREIGN KEY (`keyword_1`, `keyword_2`) REFERENCES `related_keywords` (`keyword_2`, `keyword_1`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ck_rel_kw_differ` CHECK (`keyword_1` <> `keyword_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 

Чтобы добавить одно, необходимо также добавить другое. Аналогично, удаление одной строки должно привести к удалению другой. Однако я не могу добавить первый, не добавив второй, из-за проверки внешнего ключа, обеспечивающей это:

 INSERT INTO related_keywords (keyword_1, keyword_2) VALUES
    (119250770368532480, 119251038153871360),
    (119251038153871360, 119250770368532480);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`keyword_db`.`related_keywords`, CONSTRAINT `fk_related_keyword_lr` FOREIGN KEY (`keyword_1`, `keyword_2`) REFERENCES `related_keywords` (`keyword_2`, `keyword_1`) ON DELETE CASCADE ON UPDATE )
 

Теперь я попытался запустить его в транзакции, но, похоже, он все еще проверяет внешние ключи (что мне показалось странным). Единственный способ, которым я могу его добавить, — это отключить проверку внешних ключей во время ВСТАВКИ, что мне действительно не нравится.

Идеальным решением здесь было бы сделать так, чтобы на прикладном уровне все, что мне нужно было сделать, это:

 INSERT INTO related_keywords (keyword_1, keyword_2) VALUES (119250770368532480, 119251038153871360);
 

и соответствующая строка также будет автоматически добавлена, без каких-либо проблем с fk_related_kw_lr проверкой внешнего ключа.

Я просмотрел документы для MariaDB и InnoDB в целом, и даже с более новыми функциями MariaDB 10.x я, похоже, не могу найти способ

• Временно отключите одно (из нескольких) ограничение
• Отложите проверку ограничений до конца транзакции
• …а также вставьте соответствующую строку в противоположном направлении при вставке первой

Есть какие-нибудь идеи?

• ПК бесполезен
• Нужен только один UNIQUE
• 3 uniques замедляет вставки.
• Оптимальное соотношение «многие ко многим»: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
• Я сомневаюсь, что вам нужен «каскад».
• Бросьте FKS, от них может быть больше проблем, чем они того стоят.
• Вместо добавления 2 строк рассмотрите возможность добавления одной строки со следующим. (Это может помочь или может усложнить ситуацию, в зависимости от того, как вы используете эту таблицу.)

     LEAST(1234, 2345),
    GREATEST(1234, 2345)