Настройка схемы MySQL «многие ко многим»

#mysql #foreign-keys #many-to-many

#mysql — сервер #внешние ключи #многие-ко-многим #mysql #»многие ко многим»

Вопрос:

Я настраиваю базу данных mysql с сообщениями и тегами, которые выглядят следующим образом:

 posts    
 ------------- -------------- ------ ----- ------------------- ---------------- 
| Field       | Type         | Null | Key | Default           | Extra          |
 ------------- -------------- ------ ----- ------------------- ---------------- 
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
[...]

tags
 ------- -------------- ------ ----- --------- ---------------- 
| Field | Type         | Null | Key | Default | Extra          |
 ------- -------------- ------ ----- --------- ---------------- 
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| tag   | varchar(255) | NO   | UNI | NULL    |                |
 ------- -------------- ------ ----- --------- ---------------- 

post_tag_map
 ------------ --------- ------ ----- --------- ------- 
| Field      | Type    | Null | Key | Default | Extra |
 ------------ --------- ------ ----- --------- ------- 
| post_id    | int(11) | NO   | PRI | NULL    |       |
| tag_id     | int(11) | NO   | PRI | NULL    |       |
 ------------ --------- ------ ----- --------- ------- 
  

Теги будут общими для нескольких сообщений; ‘red’ может использоваться в сообщениях 5 и 10.

Мой вопрос таков: как мне предотвратить удаление тега, если он используется более чем в одном сообщении, и удалить его, если это не так?

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

 CREATE TABLE `post_tag_map` (
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`post_id`,`tag_id`),
  FOREIGN KEY (`post_id`) REFERENCES posts(`id`),
  FOREIGN KEY (`tag_id`) REFERENCES tag(`id`)
)  
  

Комментарии:

1. Вам нужно добавить КАСКАД УДАЛЕНИЯ где-нибудь в вашем внешнем ключе?

2. @duffymo ПРИ КАСКАДНОМ УДАЛЕНИИ удалит тег, если я удалю карту. Я не уверен, что это то, чего я хочу, потому что, если тег используется другим сообщением, я хочу только удалить карту.

Ответ №1:

Вы можете удалить все таблицы за один раз, используя delete инструкцию, подобную этой.

 DELETE FROM post_tag_map, posts, tags
WHERE post.id = post_tag_map.post_id
AND tags.id = post_tag_map.tag_id
AND tags.id = 256;
  

Однако MySQL не дает никаких гарантий относительно порядка, в котором будут выполняться удаления. Если у вас есть внешние ключи, они могут помешать удалению.

Поэтому либо не используйте FOREIGN KEY **or** declare them with a предложение «При КАСКАДНОМ УДАЛЕНИИ».

Помните, что MyISAM не поддерживает внешние ключи, поэтому там у вас есть только удаление нескольких таблиц.

Подробнее об удалениях из нескольких таблиц здесь:http://dev.mysql.com/doc/refman/5.1/en/delete.html

Ответ №2:

Вы хотели бы объявить внешние ключи следующим образом:

 FOREIGN KEY (post_id) REFERENCES posts(id)
   ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tag(id)
   ON DELETE CASCADE
  

«при каскадном удалении» — это то, что инициирует автоматическое удаление. Обратите внимание, что каскад не будет распространяться «вверх» по другой стороне таблицы ссылок. Если вы удалите тег, исчезнут только соответствующие записи в post_tag_map, но останутся только записи, к которым они были прикреплены.

Комментарии:

1. @Marc кажется, это именно то, что я хочу, но это не работает. delete from tags where id = 6; не удаляет соответствующую запись. Также это не предотвращает удаление тега 6, поскольку он используется более чем в одном сообщении.

2. Вам действительно необходимо использовать таблицы InnoDB. Таблицы MyISAM будут игнорировать определения внешних ключей (они не вызовут синтаксической ошибки, но и не будут соблюдены).

3. @Marc Спасибо, это, должно быть, моя проблема

4. @Marc Это может быть другой проблемой, но теперь я получаю: ОШИБКА 1452 (23000): не удается добавить или обновить дочернюю строку: сбой ограничения внешнего ключа ( dp . posts_x_tags , ОГРАНИЧЕНИЕ post_x_tags_ibfk_2 ССЫЛОК НА ВНЕШНИЙ КЛЮЧ ( tag_id ) tag ( id ) В КАСКАДЕ УДАЛЕНИЯ), когда я: mysql> insert into post_x_tags(post_id, tag_id) values(1,1);

5. Вам нужна соответствующая запись ‘1’ как в таблицах тегов, так и в таблицах posts.

Ответ №3:

Возможно, вам потребуется добавить в свои объявления ВНЕШНЕГО КЛЮЧА:

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Комментарии:

1. Какое объявление вы предлагаете? Я пробовал КАСКАД УДАЛЕНИЯ, но, похоже, это не то, что я хочу, потому что, если я удаляю карту, она также удаляет тег, даже если на тег ссылается другой post.