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