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

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:

• Use a different DBMS (not an option)

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

• 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

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;

StackExchange Database Administrators Q#307549, answer score: 2