Как создать внешний ключ?

Question

[Алексей Коновалов]

Всем привет. У меня есть таблица comments в которой хранятся комментарии.
Так же есть таблицы posts, images, news. К этим всем сущностям могут быть написаны комментарии. Поэтому в таблице комментариев есть поля
type - тип сущности (posts, images, news)
id - ID сущности (id поста, id изображения, id новости)
text - текст комментария

Получается, что если мы добавили комментарий к изображению у которого ID == 577, то запись комментария в таблице comments будет содержать следующее:
type - images
id - 577
text - Какой-то текст комментария

Как правильно создать внешний ключ, чтобы при удалении изображения, удалились и комментарии к нему? Ведь приходится удалять не только по одному полю, а по двум, причем поле type абсолютно некчему привязать в таблице images

Answer 1

[Дмитрий]

Добрый день.
Переименуйте "id" для связи с изображением в таблице комментариев в "image_id".
Так же добавьте для комментария "id PRIMARY KEY AUTO_INCREMENT"

ALTER TABLE `comments`
  ADD CONSTRAINT `fk_comments_images` FOREIGN KEY (`image_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE;

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

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `comments`
  ADD CONSTRAINT `fk_comments_images` FOREIGN KEY (`image_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS=1;

Comments

[Алексей Коновалов]

Спасибо, за отклик!
Но получится, что если вдруг у меня будет больше сущностей, то мне нужно будет создать для каждой из них поле в таблице comments. Получится что таблица будет выглядеть так:

id_image - ID изображения, если коммент написан к изображению
id_post - ID поста, если коммент написан к посту
id_news - ID новости, если коммент написан к новости
id_question - ID вопроса, если коммент написан к вопросу
id_tovar - ID товара, если коммент написан к товару
text - текст комментария

Получается, что каждый раз нужно будет создавать дополнительные поля для каждой сущности, которые по итогу будут на 99% содержать 0 или NULL

Думаете это правильный подход?

[Дмитрий]

Алексей Коновалов, можно создать связующую таблицу, в которой будут содержаться только id, причём оба id будут PRIMARY KEY.
Вот как-то так

CREATE TABLE `relation` (
 `id` int(11) NOT NULL AUTO_INCREMENT, // для сущностей
 `id_comment` int(11) NOT NULL AUTO_INCREMENT, // для комментариев
)

[xzdshr]

Алексей Коновалов, конечно неправильный, вы все верно подметили. У вас идет полиморфическая связь, соотвественно обычный foreign key тут не подходит. Есть несколько подходов, как делать полиморфические связи с внешними ключами. Можете почитать например здесь.

Вот один из примеров полиморфической связи в БД (точнее отсутствия внешнего ключа на такой связи):

Table "public.comments"
    Column     |            Type             | Collation | Nullable |      Default      
---------------+-----------------------------+-----------+----------+-------------------
 id            | uuid                        |           | not null | gen_random_uuid()
 commentable_type | character varying           |           | not null | 
 commentable_id   | uuid                        |           | not null | 
Indexes:
    "comments_pkey" PRIMARY KEY, btree (id)
    "index_comments_on_commentable_type_and_commentable_id" btree (commentable_type, commentable_id)

Answer 2

[Chupaka]

По-хорошему, одна сущность — одна таблица. В вашем случае можно посмотреть на триггеры: удаляется строка — чистим базу по каким угодно параметрам.

Comments

[Алексей Коновалов]

т.е. думаете стоит создать для каждой сущности свою таблицу comments?
Тогда нужно будет сделать тоже самое и с Likes

[Chupaka]

Именно так. Котлеты — отдельно, мухи — отдельно.

[Алексей Коновалов]

Chupaka, но если я решу вывести последние комментарии пользователя, или последние комментарии на сайте. То всех мух собрать будет просто нереально...

[Chupaka]

Я вам UNION прнс =)