A declared foreign key (i.e., one enforced by the database engine) cannot tie to multiple other tables. So
group_device cannot be a foreign key to all three device tables.
You have a few options:
Have a unique table linking each type of device to the appropriate group (
You can create a
group_device view that's basically the same as the current
group_device table. You wouldn't have a true
id column (the other three columns should uniquely identify a row). You could find a way to create a unique ID across the actual
group_device_* tables if absolutely required, but that's a bit more work.
This does involve a certain amount of maintenance pain - when a new device is added, you have to create two tables (
group_device_typeN), plus you have to modify the
Multiple columns in
group_device (don't use)
You could create the
group_device table with a unique column for each possible device type (
device_typeC_id, etc.). Then, add a trigger (as shown in this answer) to ensure that all but one of these values is NULL.
Maintenance requirements are similar to the first suggestion: when a new type is added, you have to create the
device_typeN table; add the
device_typeN_id column to the
group_device table, and modify your trigger.
However, this approach is generally frowned upon. You're trying to use the built-in foreign key mechanism - but you're also trying to work around it. The trigger's code will be repetitive enough that it should be easy to maintain - but it's also easy to screw it up and not notice. And then, you could wind up with rows with multiple ID columns filled in, which is likely to cause some confusion in joins and such (if not outright bad data). It's also easy to have a typo that results in assigning a single
id value to the wrong column, with similar confusing results.
I mention it more for the sake of completeness than for any other reason.
Use a "virtual" foreign key
(This is the option that Rick James' answer covers - refer to that for more detail).
group_device as you have it defined now, where the foreign key is one column that identifies a table (
type_device) and one that identifies a row in that table (
device_id). Create code to confirm that the
device_id is a valid ID from the table in question, and to delete the appropriate
group_device row if the underlying device is deleted. It's possible this can be done in triggers (insert/update trigger on
group_device to validate new/changed data; delete trigger on each device table, to check for and remove any
group_device rows when a device is deleted).
Maintenance should simply be creating the delete trigger when you create the device table, and updating the insert/update trigger on
group_device to work with the new table.
If using this solution, I recommend that you either use the full table name for
type_device; have a
type_device table listing the
device_type* tables with an ID, and use that ID in
group_device; or (at a minimum) establish a strict naming convention for
device_typeN tables, so you know that
CONCAT('device_' + type_device) will always yield the correct table name.