Mysql : foreign key relative to multiple tables

Question

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).

Answer 1

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.

Answer 2

A declared foreign key (i.e., one enforced by the database engine) cannot tie to multiple other tables. So id_device in group_device cannot be a foreign key to all three device tables.

You have a few options:

Multiple group_device tables

Have a unique table linking each type of device to the appropriate group (group_device_typeA, group_device_typeB, group_device_typeC, etc.).

You can create a group_device view that's basically the same as the current group_device table. You wouldn't have a true group_device.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 (device_typeN and group_device_typeN), plus you have to modify the group_device view.

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_typeA_id, device_typeB_id, 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).

Use 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.

Answer 3

Here's one sketch. Introduce a generic DEVICES table.

CREATE TABLE devices
( device_id ... NOT NULL
, device_type ... NOT NULL
, ... -- generice attributes
, PRIMARY KEY (device_id)
, UNIQUE (device_type, device_id)
, CHECK (device_type IN ('A','B','C')) 
);

CREATE TABLE devices_typeA
( device_id ... NOT NULL
, device_type ... NOT NULL
, ... -- type_A specific attributes
, PRIMARY KEY (device_id)
, CHECK (device_type = 'A') -- introduced in 8.0.16
, FOREIGN KEY (device_id, device_type)
      REFERENCES devices (device_id, device_type)
          ON DELETE CASCADE -- I assume

Some notes, a FOREIGN KEY can reference a UNIQUE constraint, not only the PRIMARY KEY. That together with CHECK constraint can guarantee that the correct device_type is used in devices.

For MySQL < 8.0.16 CHECK CONSTRAINTS did not exist (introduced SQL92, 199x). Mimic them with ENUM may seem like a good idea at first, DONT!!!, see for example: how-does-mariadb-handle-enum-types-used-in-foreign-key-constraints

Now GROUP_DEVISES can reference GROUPS as well as DEVICES

CREATE TABLE group_devices
( group_id ...
, device_id ...
, PRIMARY KEY (group_id, device_id)
, FOREIGN KEY (group_id) 
      REFERENCES groups (group_id) 
          ON DELETE CASCADE
          ...  
, FOREIGN KEY (device_id) REFERENCES devices (device_id)
      REFERENCES devices (device_id) 
          ON DELETE CASCADE
          ...

If a device is deleted, the delete is cascaded to both device_type... as well as to group_devices.