Search code examples
mysqlforeign-keyspolymorphic-associationscheck-constraints

MySQL - Multiple foreign keys, that are only valid on individual check constraints


Title says pretty much what I need but I want to see how I can take two foreign key constraints from a table, and two check constraints and associate one check constraint, while associating the second check constraint with the second foreign key constraint.

Example, I have two 3 tables, item, action, risk. Item references action or risk, by itemid only I want conditional references on more than one foreign key with each foreign key having one unique check constraint.

I will use itemtype ( 'Action' or 'Risk') in my check constraint to determine what table I am referencing.

Here is my command:

ALTER TABLE `projectaim`.`items`  
              ADD CONSTRAINT `fk_item_risk` FOREIGN KEY (`ItemID`) REFERENCES `projectaim`.`risks`(`RiskID`)
ADD CONSTRAINT ck_item_type CHECK (itemtype = 'Risk') 

Is this at all possible in MySQL?


Solution

  • It sounds like you're trying to implement , where a given column can be an id referencing any of several parent table.

    A foreign key constraint references one parent table. You can't make these conditional. This is why polymorphic associations are fundamentally not compatible with relational constraints.

    If you need to reference one of several parent tables, one way to do it is to create multiple columns, each of which are nullable. Then you can use a check constraint or a trigger to make sure that exactly one of them is not null.

    CREATE TABLE items (
      Itemid INT NOT NULL PRIMARY KEY,
      Itemtype ENUM ('Action', 'Risk') NOT NULL,
      Actionid INT NULL,
      Riskid INT NULL,
      FOREIGN KEY (Actionid) REFERENCES actions (Actionid),
      FOREIGN KEY (Riskid) REFERENCES risks (riskid),
      CHECK (CASE Itemtype
             WHEN 'Action' THEN Actionid IS NOT NULL AND Riskid IS NULL
             WHEN 'Risk' THEN Riskid IS NOT NULL AND Actionid IS NULL
             END)
    );
    

    See also: