Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 208
Shoe Diamente Avatar asked Apr 22 '20 09:04

Shoe Diamente


People also ask

Can you have a foreign key without a constraint?

Foreign Keys without the constraintsYou don't have to configure a foreign key constraint on a column just because it refers to another column. You could instead configure two tables such that one refers to the other, but without any defined foreign key.

What are the limitations of check constraint?

The condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub queries, sequence, the SYSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

Can a foreign key reference a foreign key?

A foreign key can reference any field defined as unique. If that unique field is itself defined as a foreign key, it makes no difference.

Can a foreign key reference a UNIQUE constraint?

A UNIQUE constraint can be referenced by a FOREIGN KEY constraint. When a UNIQUE constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure all values are unique.


1 Answers

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

like image 61
GMB Avatar answered Oct 02 '22 17:10

GMB