#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.