Replicate 'ON DELETE SET NULL' behavior from combination of two non-unique columns on another table

Question

The Situation

I have a full-stack web application with two MySQL tables: channel_strips and mic_lookup.

DESCRIBE `channel_strips`;

Field           Type            Null    Key
preset_id       varchar(127)    NO      PRI 
mic_function    varchar(225)    YES
phantom_power   tinyint(1)      YES
...

DESCRIBE `mic_lookup`;

Field           Type            Null    Key
Microphone      varchar(40)     NO      PRI 
mic_function    varchar(225)    NO
phantom_power   tinyint(1)      NO
...
(many other columns)

I want the channel_strips table to always hold only combinations of mic_function and phantom_power values that can be currently found in the mic_lookup table (or null values).

What is working

On the HTML end, I've limited the input to these columns in channel_strips with a <select> element that gets values from this mysqli query: SELECT DISTINCT `mic_function`, `phantom_power` FROM `mic_lookup`; This successfully restricts the user input.

The Problem

The one situation I've identified where this fails is when entries are deleted or changed in mic_lookup such that one pre-existing combination of mic_function and phantom_power is eliminated. As a result, channel_strips could still have a combination of the two columns that is actually no longer an option. In this situation, I'd like those two columns to be nullified on rows where they hold the old combination, essentially emulating an ON DELETE SET NULL statement as if it were a foreign key.

What I've tried

For a while, I had an intermediate table, mic_functions with a single column, mic_function, which served as a foreign key to both tables' mic_function columns. However, this was before I realized that phantom_power needed to be included. Furthermore, it was very confusing from a user's perspective, since intuitively you would want to set these values in the mic_lookup table.

My next idea was to create a view instead so I'd have a 'table' that automatically updates, and reference that as a foreign key - maybe something like...

CREATE VIEW mic_functions AS(
    SELECT DISTINCT
        `mic_function`,
        `phantom_power`
    FROM
        `mic_lookup`
);
ALTER VIEW mic_functions ADD CONSTRAINT PK_mic_function PRIMARY KEY(`mic_function`, `phantom_power`);

Of course, this doesn't work. You can't add a primary key to a VIEW.

Finally, I suppose I could write a bunch of php to query and perform a series of checks on channel_strips every time the mic_lookup table is updated, and execute an appropriate UPDATE query if the checks are violated, but it seems to me like there ought to be a simpler way to handle this on the SQL side of things. Maybe using SQL checks or triggers or a combination of the two would work, but I have no experience with checks and triggers.

Note

phantom_power is boolean, and I'm using MySQL version 10.4.21-MariaDB

Answer 1

I found a solution using triggers, by joining the tables and filtering down to where the joined primary key is null:

CREATE TRIGGER `mic_lookup_update` AFTER UPDATE
ON
    `mic_lookup` FOR EACH ROW
UPDATE
    `channel_strips` c
LEFT JOIN `mic_lookup` m ON
    c.mic_function = m.mic_function AND c.phantom_power = m.phantom_power
SET
    c.mic_function = NULL,
    c.phantom_power = NULL
WHERE
    m.Microphone IS NULL;

CREATE TRIGGER `mic_lookup_delete` AFTER DELETE
ON
    `mic_lookup` FOR EACH ROW
UPDATE
    `channel_strips` c
LEFT JOIN `mic_lookup` m ON
    c.mic_function = m.mic_function AND c.phantom_power = m.phantom_power
SET
    c.mic_function = NULL,
    c.phantom_power = NULL
WHERE
    m.Microphone IS NULL;