Подтвердить что ты не робот

MySQL - Условные внешние ограничения ключа

В моем приложении следующая таблица комментариев:

comments
--------
id           INT
foreign_id   INT
model        TEXT
comment_text TEXT
...

Идея этой таблицы заключается в том, чтобы хранить комментарии для различных частей моего приложения - она может хранить комментарии для сообщения в блоге i.e:

1|34|blogpost|lorem ipsum...

изображение пользователя:

2|12|picture|lorem ipsum...

и т.д.

теперь существует ли способ принудительного ограничения FOREIGN KEY для таких данных?

то есть. что-то вроде этого в таблице комментариев:

FOREIGN KEY (`foreign_id`) REFERENCES blogposts (`id`)
//but only when model='blogpost'

Ответ 1

Вы пытаетесь сделать дизайн, который называется Полиморфные ассоциации. То есть внешний ключ может ссылаться на строки в любой из нескольких связанных таблиц.

Но ограничение внешнего ключа должно ссылаться только на одну таблицу. Вы не можете объявить внешний ключ, который ссылается на разные таблицы в зависимости от значения в другом столбце таблицы Comments. Это нарушит несколько правил проектирования реляционных баз данных.

Лучшим решением является создание своего рода "надёжного", на которое ссылаются комментарии.

CREATE TABLE Commentable (
  id SERIAL PRIMARY KEY
);

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  foreign_id INT NOT NULL,
  ...
  FOREIGN KEY (foreign_id) REFERENCES Commentable(id)
);

Каждый из ваших типов контента будет считаться подтипом этого надёжного. Это аналогично объектно-ориентированной концепции интерфейса.

CREATE TABLE BlogPosts (
  blogpost_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (blogpost_id) REFERENCES Commentable(id)
);

CREATE TABLE UserPictures (
  userpicture_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (userpicture_id) REFERENCES Commentable(id)
);

Прежде чем вы сможете вставить строку в BlogPosts или UserPictures, вы должны вставить новую строку в Commentable, чтобы сгенерировать новый псевдоним. Затем вы можете использовать этот сгенерированный идентификатор, когда вы вставляете содержимое в соответствующую таблицу подтипов.

Как только вы сделаете все это, вы можете положиться на ограничения ссылочной целостности.