MySQL: Foreign key constraint on composite key with the referenced table a function of a constituent column of the key

Question

I have two MySQL tables customFieldsText and customFieldsMultipleChoice. Both tables have an auto-incremental unsigned int primary key.

I'd like to create a new table called customFields with the following two columns:

customFields
 - id [int] [unsigned] [auto-increment]
 - type [string] [not nullable]
 - foreignId [int] [unsigned] [not nullable]

I'd like to enforce that [type, foreignId] is a unique key on this table. I would like to have "something like" a foreign key constraint that works this way:

if type == 'text' then 
   enforce that foreignId can be found in customFieldsText table
if type == 'multiple_choice' then
   enforce that the foreignId can be found in the customFieldsMultipleChoice table

I'm not too experienced with SQL and I don't know if this is possible at all. I think it's probably clear what I want to achieve here: I want to be able to uniquely identify all my customFields by a single unsigned integer key. I inherited the two tables mentioned above so I'd be disinclined to change their schema. How would you solve this issue? Is there a way to create such a foreign key constraint?

Answer 1

Maybe it's not exactly what you expected but you can try such table structure:

CREATE TABLE customFields (
    id INT UNSIGNED NOT NULL
    ,type VARCHAR(15) NOT NULL -- it would be better to use bit
    ,foreignIdMultipleChoice INT NULL    
    ,foreignIdText INT NULL
    ,foreignId INT AS (IFNULL(foreignIdMultipleChoice, foreignIdText))
    ,CONSTRAINT FK_foreignIdMultipleChoice FOREIGN KEY (foreignIdMultipleChoice)
        REFERENCES customFieldsMultipleChoice(id)
    ,CONSTRAINT FK_foreignIdText FOREIGN KEY (foreignIdText)
        REFERENCES customFieldsText(id)
    )

You will need to insert foreignId value into one of these columns (foreignIdMultipleChoice or foreignIdText) depending on the field source. If you are on latest version of MySQL you can add check constraint to avoid issues when both of these columns are nulls or both of them are not null simultaneously. Something like this:

,CONSTRAINT `foreignId_check` CHECK ((foreignIdMultipleChoice > 0 AND foreignIdText IS NULL) 
                                      OR (foreignIdMultipleChoice IS NULL AND foreignIdText > 0))