7

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Shoe Diamente
  • 723
  • 1
  • 5
  • 24

1 Answers1

6

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!).

Giffyguy
  • 20,378
  • 34
  • 97
  • 168
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 2
    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? – Shoe Diamente Apr 22 '20 at 09:54
  • @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. :( – Giffyguy Oct 28 '20 at 20:24
  • @ShoeDiamente, did you end up using a trigger? In hindsight, any idea why this limitation might have been put in place? – Magnus Lind Oxlund Feb 27 '23 at 18:26