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:

 - 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?

  • Your FK seems to be backwards; customFieldsText and customFieldsMultipleChoice should probably reference customFields as their parent. – mustaccio Jun 11 at 17:48
  • why do you need this single integer key? – Nikita Jun 11 at 18:03
  • 1
    Sometimes FKs are more trouble than they are worth. – Rick James Jun 11 at 18:13
  • @Nikita I wanna have a superclass to my ‘customFieldMultipleChoice’ and ‘customFieldText’ models in my codebase. Why does this mean that I need a unique integer key? Well I would like to instantiate customField subclasses by just passing one id. It’s about convenience. – gen Jun 11 at 22:23
  • @RickJames thanks for adding this comment, it brought me some peace :) – gen Jun 15 at 9:08

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

CREATE TABLE customFields (
    ,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))
| improve this answer | |
  • Thank you for posting this! Yes, I can see this working, however why I am disinclined to go with this approach is that I fear someday I'll have a customFieldImage or a customFieldFileUpload and so I'll have to keep adding columns to this table... – gen Jun 15 at 9:07
  • @gen another solution - you can try to split id ranges (use 1 - 1 000 000 for customFieldsText, 1 000 001 - 2 000 000 for customFieldsMultipleChoice) and create view customFields as select id, 'text' as type from customFieldsText union all select id, 'multiple_choice' from customFieldsMultipleChoice – Nikita Jun 15 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.