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

Question

[hrvasiliy]

Пытаюсь реализовать связь между типами транспорта с использованием внешних ключей. В чем загвоздка? - В том, что на данный момент предки ссылаются на потомка, получается, что у одного потомка 4 родителя - что является бредом. И что интересно MySQL Workbench разрешает построить такую диаграмму. Родитель должен как-то учитывать тип транспорта, чтобы при изменении ID родителя изменялись именно те ID, которые соответствуют типу. Не могу понять, как это сделать.

Comments

[sim3x]

Зачем копировать впрос? Как правильно спроектировать БД?

Answer 1

[Виталий Витренко]

Как вам такое?

И вы уверены в том что разные виды транспорта стоит разносить по разным таблицам? Если их поля не будут особо отличатся, можно подумать о том как соединить все в одну таблицу.

Comments

[hrvasiliy]

Вы не могли бы выложить исходник? Весь день сижу над этим, плохо голова работает :) По поводу того, чтобы соединить, возможно вы и правы, но я намеренно их разделял, потому что достаточное количество полей будут пусты, так же как минимум один из типов будет иметь подпункты, скажем так. Даже не знаю, стоит ли оно того, что половина полей будут просто со значением null?

[Виталий Витренко]

hrvasiliy: sqlfiddle.com/#!2/e5085

[hrvasiliy]

Виталий Витренко: Если я изменю ID в таблице 'user_transport' этот ID сменится и у bus, boat, car... Выходит, что ваш способ не учитывает тип

[Виталий Витренко]

hrvasiliy: Ну можно написать триггер который разрешает добавлять в каждую таблицу только тот транспорт, тип которого она представляет. И тогда вы будете уверены что 1 id из user_transport будет только в одной дочерней таблице.

Answer 2

[Aleksey Ratnikov]

Указанная вами схема не может быть реализована через FOREIGN KEY, можно через триггеры на таблицах "родителей". Либо я бы рекомендовал поместить всех родителей и их типы (если я все правильно понял и это их типы) в одну таблицу с примерно такой схемой:

CREATE TABLE trasports (
id INT UNSIGNED NOT NULL,
type INT UNSIGNED NOT NULL,

PRIMARY KEY (id, type)
FOREIGN KEY (type) REFERENCES transport_type (id)
);

И внешний ключ в user_transport делать по двум полям:

ALTER TABLE user_transport ADD FOREIGN KEY (transport_type, transport_id) REFERENCES trasports (type, id) ON UPDATE CASCADE;

Comments

[hrvasiliy]

Правильно я понимаю? i.imgur.com/tfspNWW.png

[Aleksey Ratnikov]

Да, примерно так, только ограничитель transport_type + transport_id важно указывать на таблицу transports для гарантирования согласованности. Приложил схему к ответу.