I have two MySQL tables
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?