0

can you please help me to resolve next problem: I have one main table (Vehicles) and four dependent tables (different types of vehicles: passenger_car, mini_bus ... etc). (i will provide for you just for example only two of them: main and one dependent (passenger_car), because the rest of them are the similar structure, only are differ types). my sql schema example

In main table (vehicle) there will be common fields for all of vehicles types, and the part that is different will be stored in dependent tables (for Passenger car in passenger_car, for Mini Bus in mini_bus etc). I will need to write trigger for insert and update operation for passenger_car table to restrict input in vehicle_id (foreign key) id from table (vehicles) which are relate to record with vehicle_type value is not equal to dependent table type of vehicle.

for example: vehicle table

id vehicle_type
1 passenger_car
2 mini_buss

passenger_car table

id vehicle_id
1 1 (<- is allowed)
2 2 (<- NOT allowed!!!)

mini_bus table

id vehicle_id
1 1 (<- NOT allowed!!!)
2 2 (<- is allowed)

Can you please help me to write such a trigger, because my knowledge of SQL is not good enough at a moment.

I tried to read documentation in MySQL and search for same problem in stackoverflow but anyway i don`t understand how to write correct trigger expression.

Alex Xela
  • 1
  • 2
  • Do not use triggers, ad vehicle type field to your parent table and the child table and make them part of the foreign key. – Shadow Nov 25 '22 at 14:56
  • do you mean like this? vehicle table: PRIMARY KEY (`id`, `vehicle_type`) and passenger_car table: FOREIGN KEY (`vehicle_id`, `vehicle_type`) REFERENCES vehicle (`id`) ON DELETE CASCADE – Alex Xela Nov 25 '22 at 15:07
  • You need vehicle_type in the references part of the FK, otherwise yes, that's what I meant. See the main answer to the duplicate question for a lot more details on the theory behind this. – Shadow Nov 25 '22 at 17:13

0 Answers0