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).
4 Answers 4
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.
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.
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.
A foreign key by theory establishes a relationship with another table - it isn't possible with others, so what you're asking goes beyond relational databases foreign keys scope.
The comments above already mention solutions for your problem, i'm not going to repeat them. I'll limit myself to mention a concept: Polymorphic Relationships. It's present in a lot of ORMs - so we're talking about application level.
in summary, it consists in "having not only a foreign key column" - which is not actually a FK in database-level, i'd rather call a "fake foreign key" - and another that identifies to which table the "fake foreign key" refers to.
That's an example below in Laravel's default ORM (Eloquent):
posts
id - integer
name - string
users
id - integer
name - string
images
id - integer
url - string
imageable_id - integer
imageable_type - string
Ref: https://laravel.com/docs/11.x/eloquent-relationships#polymorphic-relationships