Foreign key from one column to two (fixed computed column)?

Question

I am trying to set up some foreign keys for some tables and I have an inheritance scenario with a "supertable" and some "subtables".

The basic structure is that the supertable contains a column, and each possible unique value for that column has a subtable with child-specific information.

e.g.

super
type | name | etc
sub
name | etc

In super, type, name is the composite primary key. That is, name by itself is not necessarily unique (though within any particular child table, it would be).

Is there any way to enforce this constraint in the schema between tables using foreign keys, without adding a dummy column with a default value set to that type?

What I am trying to avoid is this:

super
type | name | etc
1 | a | asdf
1 | b | asdf
sub
type | name | etc
1 | a | asdf
1 | b | asdf

...because then the subtable just has a dummy column that will only ever be a single value, wasting space. However, without that dummy column, I am currently unsure how to add the foreign key since I basically want to add the foreign key on sub.name to super.name, WHERE super.type = 1, if that makes any sense.

This answer sort of describes what I am trying to do: https://dba.stackexchange.com/a/302243/207865

However, this is for SQL Server and doesn't seem to work for MySQL/MariaDB.

It seems like my options are:

1. Use a different DBMS (not an option)
2. Go ahead and just add a sub.type with DEFAULT 1 which will never used in any queries or for anything besides the composite foreign key constraint to the super table.
3. Add another unique ID column to both super and sub (AUTO INCREMENT) and use that instead of type or name at all.

Both the latter options are undesirable as they would just add information I don't really need.

What would make the most sense? Are there even other options?

To be clear, I know that I can form my queries properly by adding the appropriate ON constraint here when joining.

I'm just wondering if there's any way to enforce a constraint here in the schema, so that if super(1,A) was deleted, then sub(A) would also get deleted, but not sub2(A), for instance.

MariaDB version: 10.3.31-MariaDB-0+deb10u1 Debian 10

• ypercubeᵀᴹ: Option 5: a single table with all the additional information of the subtype tables in nullable columns. Your subtype 1 table would then be a view with something like: CREATE VIEW subtype1 AS SELECT <list_of_common_and_type1_columns> FROM supertable WHERE type = 1
• outis: Related: "How to model inheritance in MySQL for adding and deleting users?" (currently unanswered), "Representing a Superclass/Subclass (or Supertype-Subtype) relationship in a MySQL Workbench diagram" Also related (from SO): "How to Implement Referential Integrity in Subtypes".
• outis: Related terms for this type of relationship/model (from Entity Relationship Modeling): supertype-subtype; basetype-subtype; inheritance; category; superclass-subclass; generalization-specialization; generalization hierarchy.

Answer 1

Not that you were considering this, but note that based on the syntax for FOREIGN KEYs, they by themselves do not offer a solution (as the members of the key must be columns) (see "Can a MySQL FOREIGN KEY CONSTRAINT reference a column value rather than a column name?").

Surrogates

I (and many others) would argue that a surrogate key (option 3) is not "unnecessary information". The short version is that surrogate keys add a bit of abstraction that buffers the data model from things like schema changes and supports more data integrity constraints (such as foreign key constraints). A surrogate key is not based on any attribute of what's modeled, but that's the source of its strength.

Generated Columns

MariaDB supports generated columns in some storage engines using basically the same syntax as SQL Server's computed columns. For them to be used in foreign keys in older versions of MariaDB, they must have PERSISTENT storage (which stores them in the table). According to tests by dbdemon, newer versions also support VIRTUAL storage (which generates the column values when the table is queried) for generated columns in foreign keys. With any version, you cannot use ON UPDATE CASCADE, as that might cause a row to no longer be valid for the table, and INSERT cascades aren't a thing, so a generated column in a foreign key only helps with deletion.

Triggers

If a surrogate key is truly not an option, you can implement various parts of constraints using triggers: update and delete (and even insert) cascades, fixing inconsistent data (where appropriate), and more general checks.

For both flexibility and readability, have the triggers call procedures for the actual table operations, assuming the columns are fairly consistent across tables. All the insertion and deletion triggers would then need to do is call the related procedures. The update trigger would need to determine whether the type is getting changed (in which case the row would need to be deleted from the old table and inserted into the new) or staying the same (in which case the corresponding row can simply be updated). Alternatively, the update trigger could prevent the column from being changed.

If triggers could execute dynamic SQL, a single prepared SQL statement would do for each DML statement (INSERT, UPDATE, DELETE) in the procedures, but triggers in MySQL/MariaDB are limited so they can't execute dynamic SQL, not even indirectly by calling procedures. Consequently, the stored procedures below are a little more verbose by having a CASE statement in each one that chooses the exact DML statement to execute. At least only one procedure for each DML statement is called for.

However, if the ancillary tables have few similar columns in common, stored procedures aren't useful, and the DML should instead go in the triggers.

Hopefully, the type column is implemented as an ENUM in the actual schema for greater data integrity. These enum values could then be treated as INTs (as is done in the sample SQL) or as a CHARs (as is done below).

Only minimal error handling is done in this example. Transactions would be useful for error handling, but they aren't allowed in triggers; fortunately, savepoints are. The UPDATE trigger sets a savepoint and defines an error handler that rolls back to the savepoint if there's any type of error.

The below has only been tested on MySQL 5.7, not MariaDB.

DELIMITER ;;
CREATE PROCEDURE signal_unknown_type
 (type CHAR(32))
BEGIN
 SET @msg = Concat('No known subtable for type: "', type, '"');
 SIGNAL SQLSTATE '42S02'
 SET MESSAGE_TEXT = @msg,
 SCHEMA_NAME = '...',
 TABLE_NAME = 'super', 
 ...;
END;;
CREATE PROCEDURE super_insert_sub
 (type CHAR(32), name VARCHAR(64), ...)
BEGIN
 CASE type
 WHEN 'a' THEN INSERT INTO sub_a (name, ...) VALUES (name);
 WHEN 'b' THEN INSERT INTO sub_b (name, ...) VALUES (name);
 ELSE CALL signal_unknown_type(type);
 END CASE;
END;;
CREATE PROCEDURE super_update_sub
 (type CHAR(32), old_name VARCHAR(16), new_name VARCHAR(16), ...)
BEGIN
 CASE type
 WHEN 'a' THEN UPDATE sub_a SET name = new_name, ... WHERE name = old_name;
 WHEN 'b' THEN UPDATE sub_b SET name = new_name, ... WHERE name = old_name;
 ...
 ELSE CALL signal_unknown_type(type);
 END CASE;
END;;
CREATE PROCEDURE super_delete_sub
 (type CHAR(32), name VARCHAR(16))
BEGIN
 CASE type
 WHEN 'a' THEN DELETE FROM sub_a WHERE name = name;
 WHEN 'b' THEN DELETE FROM sub_b WHERE name = name;
 ...
 ELSE CALL signal_unknown_type(type);
 END CASE;
END;;
CREATE TRIGGER `insert_sub_from_super` 
 AFTER INSERT ON `super` FOR EACH ROW
BEGIN
 CALL super_insert_sub(new.type, new.name);
END;;
CREATE TRIGGER `update_sub_from_super` 
 AFTER UPDATE ON `super` FOR EACH ROW
BEGIN
 DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND, SQLEXCEPTION
 BEGIN
 ROLLBACK TO `update_sub_from_super`;
 RESIGNAL;
 END;
 SAVEPOINT `update_sub_from_super`;
 IF old.name = new.name THEN
 CALL super_update_sub(new.type, old.name, new.name, ...);
 ELSE
 CALL super_delete_sub(old.type, old.name);
 CALL super_insert_sub(new.type, new.name, ...);
 END IF;
 RELEASE SAVEPOINT `update_sub_from_super`;
END;;
CREATE TRIGGER `delete_sub_from_super` 
 AFTER DELETE ON `super` FOR EACH ROW
BEGIN
 CALL super_delete_sub(old.type, old.name);
END;;
DELIMITER ;

• dbdemon: MariaDB 10.2.3+ support foreign keys (indexes) using virtual columns, see mariadb.com/kb/en/generated-columns/#index-support . Therefore, the following would work: alter table sub add type int(10) unsigned GENERATED ALWAYS AS (1) virtual; alter table sub add constraint sub_fk_type_name foreign key(type, name) references super(type, name) on delete cascade on update no action;
• InterLinked: @dbdemon Ah, this is perfect, shame that cascade on update doesn't work though. I set mine to restrict rather than no action as I wouldn't want the two to get out of sync somehow.
• InterLinked: @dbdemon To add on, this solution works as you indicated, though being able to update name would be very nice. Right now, I have to delete the row and re-insert. I can see why, I suppose - because the computed column won't change - but in this case it never will change, so it seems a bit silly. Is there any way to do this?
• outis: @dbdemon: the document says 10.2.3+ supports indices on virtual columns, but that section doesn't say foreign keys or constraints. The way the document is written (with the statement about foreign keys separate from indices, and the two sections about ≥10.2.3 and ≤10.2.2 only mentioning indices) seems to say that foreign keys are not supported for virtual columns. Is the documentation incorrect/unclear on this point?
• dbdemon: outis: Yes, it does seem a little unclear. I have however successfully tested the above alter statements, although on a newer version of MariaDB. @InterLinked I can't think of any other solution for updates of name than a trigger on updates of super.