Is it not possible to have a check constraint reference a column that also has a foreign key?

Question

I'm on MySQL 8 trying to add a check constraint:

ALTER TABLE `table` ADD CHECK (
    (`column_a` IS NULL AND `column_b` IS NOT NULL) OR
    (`column_a` IS NOT NULL AND `column_b` IS NULL)
);

but I keep getting this error:

Column 'column_b' cannot be used in a check constraint 'table_chk_1': needed in a foreign key constraint 'table_ibfk_2' referential action.

I can't find any reference to this error anywhere else on the internet and I don't understand what the problem is. Both column_a and column_b are also foreign keys to other tables and they are both nullable. I just want to make sure that each row in table has either a reference via column_a or via column_b.

What is the cause of this error?

---

What have I tried

I've tried to drop the foreign keys, add the check constraints and it succeeds. Then if I add the foreign key back to column_b I still get the same error.

Macaroni

It seems to be referring specifically to a referential action. That would be something like ON DELETE CASCADE being specified in an FK. – Dysphagia

Plese share the create table statement for your table. – Gonococcus

Answer 1

This is a documented behavior:

Foreign key referential actions (ON UPDATE, ON DELETE) are prohibited on columns used in CHECK constraints. Likewise, CHECK constraints are prohibited on columns used in foreign key referential actions.

So you need to choose between having a referential action on your column, or having a check constraint. Alternatively, you can keep the referential action and implement the check logic using triggers (or keep the check constraint and implement the referential action in a trigger!).

Gonococcus

Oh yeah, apparently this was caused by a ON UPDATE CASCADE for column_b. Hmm, I'm not sure I understand the rationale behind this behavior? Do you have any insights perhaps? – Macaroni

@ShoeDiamente Running into this same problem myself. Super annoying. MySQL has many aspects with questionable rationale, and I guess we just have to live with it. :( – Byte

@ShoeDiamente, did you end up using a trigger? In hindsight, any idea why this limitation might have been put in place? – Gorga