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?
Best Answer
Maybe it's not exactly what you expected but you can try such table structure:
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: