Mysql : foreign key relative to multiple tables I'm trying to improve the database integrity adding some foreign key (it's the first time i use them).

I have some tables relative to different type of devices (different tables because the "devices" are completely different from each other), a table groups and a relation-table group_device (the same device can be in more groups).



devices_typeA

id is primary key

id | column1 | column2 | 
------------------------
1  | aaa     | bbbb    |
2  | aaa     | bbbb    |




devices_typeB

id is primary key

id | column1 | column2 | column2 | columnN | 
--------------------------------------------
1  | aaa     | bbbb    | cccc    | dddd    |
2  | aaa     | bbbb    | cccc    | dddd    |




devices_typeC

id is primary key

id | column1 | 
--------------
1  | aaa     | 
2  | aaa     |




groups

id is primary key

id | name    | 
--------------
1  | group 1 | 
2  | group 2 | 
3  | group n |  




group_device

id | type_device | id_device | id_group |
-----------------------------------------
1  | typeA       | 1         | 1        | 
2  | typeA       | 1         | 2        | 
3  | typeB       | 1         | 1        | 
4  | typeC       | 2         | 3        | 




Now i would like to add some foreign key to the tables group_device (in such a way that when a device is deleted, is deleted also in the table group_device [with the ON DELETE CASCADE action]). My problem is the id_device column, that is relative to multiple tables (devices_typeA.id, devices_typeB.id , devices_typeC.id).
FOREIGN KEYs have severe limitations.  I think you have exceeded them.

Complex data structures cannot depend on FKs for integrity, your code is required.

Suggest that you build Stored Routines and/or subroutines in your application language, and encapsulate the integrity constraints there.  And build a clean API into the routines to make it easy for the clients to achieve their goals while the routine achieves the integrity goals.