MySQL - условные ограничения внешнего ключа
У меня есть следующая таблица 'комментариев' в моем приложении:
comments
--------
id INT
foreign_id INT
model TEXT
comment_text TEXT
...
идея этой таблицы состоит в том, чтобы сохранить комментарии для различных частей моего приложения - это может сохранить комментарии для сообщения в блоге т.е.:
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, чтобы сгенерировать новый псевдоключевой идентификатор. Затем вы можете использовать сгенерированный id при вставке содержимого в соответствующую таблицу подтипов.
После того, как вы сделаете все это, вы можете полагаться на ограничения целостности ссылок.
.