CODEWITHSUNDEEP
mysqlforeign-keyspolymorphic-associationscheck-constraints
Vahe
Vahe

Reputation: 1841

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?

Upvotes: 1

Views: 1739

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562250

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:

Upvotes: 3

Related Questions