How to make a composite foreign key?

Question

MySQL

I'm trying to implement a relationship between transport types using foreign keys. What's the catch? - In the fact that at the moment the ancestors refer to the descendant, it turns out that one descendant has 4 parents - which is nonsense. And interestingly, MySQL Workbench allows you to build such a diagram. The parent must somehow take into account the type of transport, so that when the parent's ID changes, exactly those IDs that correspond to the type change. Can't figure out how to do it.

Answer 1

How do you like that?
And are you sure that different types of transport should be distributed according to different tables? If their fields are not very different, you can think about how to combine everything into one table.

Answer 2

The scheme specified by you cannot be implemented through FOREIGN KEY, it is possible through triggers on tables of "parents". Or I would recommend putting all parents and their types (if I understood everything correctly and these are their types) in one table with something like this:

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

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

And make the foreign key in user_transport by two fields: