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

Question

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?

I appreciate the comment, I am concluding I might need a stored procedure here. — Vahe, Apr 10 '19 at 21:37
However, check constraints have a number of limitations. You can't perform a subquery in them, so that prevents using `CHECK` to validate a foreign key. There's no way to make foreign keys conditional. — Barmar, Apr 10 '19 at 21:40

Bill Karwin · Accepted Answer · 2019-04-10T21:59:52.483

3

It sounds like you're trying to implement polymorphic-associations, 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:

edited Apr 10 '19 at 21:59

answered Apr 10 '19 at 21:57

Bill Karwin

538,548
86
673
828

Thank you or clarifying the actual method, by name, I am targeting here. – Vahe Apr 10 '19 at 21:59
Hi, is it good design to have two foreign keys that one of them can be null? – Arash Sep 05 '21 at 13:48
@Arash, In the solution I show here, both foreign keys must allow nulls. But on a given row, one must be null and the other must be non-null. – Bill Karwin Sep 05 '21 at 16:15
Thanks, @Bill. Sorry i'm not good at English; I didn't ask my questions well. I meant in a one row. Is it good practice or should i redesign my tables? – Arash Sep 05 '21 at 16:44
@Arash, Polymorphic associations are complex, and it's not possible to say one solution is right for all cases. You have to evaluate different solutions and pick the one that is right for your project. I don't know your project so I can't say which is best. – Bill Karwin Sep 05 '21 at 18:52

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

1 Answers1