Foreign key issue

[Dries Verschoren]

Dear all,

I am struggling with interconnecting some different tables together with foreign keys.
We are using the foreign keys to make sure that if we update certain data, that everything in the database is updated accordingly and that we do not have to create stored procedures or something when updating certain values inside the different tables.

The main issue is that inside the table 'dadoge_steps_and_transitions' we want to make sure that the selected Module_Name + Role_Name + Control_Strategy_Name (+ Setpoint_Name) are linked.

As an example of how everything is linked, below an example of data how it should be inside the tables
Inside dadoge_module:
Module_Name = Tank
Module_Name = Stirrer

Inside dadoge_role:
Module_Name = Tank
Role_Module_Name = Stirrer
Role_Name = Mixer

Inside dadoge_control_strategy:
Module_Name = Stirrer
Control_Strategy_Name = Fixed speed

Inside dadoge_setpoints:
Module_Name = Stirrer
Setpoint_Name = SP_Speed

Inside the table 'dadoge_steps_and_transitions':
Module_Name = Tank
Role_Name = Mixer (which is a Role_Module_Name = Stirrer)
Control_Strategy_Name = Fixed_Speed
Setpoint_Name = SP_Speed

So actually Control_Strategy_Name and Setpoint_Name is the issue --> I don't see how I can make sure that when Module_Name + Role_Name is filled it. That I make sure the Control_Strategy_Name is Fixed_Speed (linked to Stirrer and not to Mixer)
I tried creating a table which combines both table = dadoge_role_cs_setpoint, but then I get issues when I delete something in the role table (it deletes everything that is linked to the Module_Name inside the combined table (dadoge_role_cs_setpoint) + in dadoge_steps_and_transitions-table)

If someone is experienced in this, please let me know!

Thanks a lot if someone is able to help me!

Kind regards,
Dries