CODEWITHSUNDEEP
mysqlsqlcreate-tablecheck-constraintsmysql-8.0
Shoe Diamente
Shoe Diamente

Reputation: 794

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

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.

Upvotes: 10

Views: 2217

Answers (1)

GMB
GMB

Reputation: 222402

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

Upvotes: 8

Related Questions