XOR Constraint in Mysql

Question

Hi.

I want to implement a kind of xor constraint on foreign keys in mysql 5.1

There is this table, let's say Entity which can refer to two different kinds of valuesets represented by Tables ValsA and ValsB. Now I would like to implement a constraint wich makes sure that exactly one of those two is mapped, and the other one isn't.

In Oracle you could use something like

CHECK (NVL2(FK_A,1,0)+NVL2(FK_B,1,0)=1));

but as far as I understand it MySQL does not really support CHECK Constraints (yet).

Any ideas?

Answer 1

Correct. MySQL does not support check contraints. The CHECK clause is parsed but ignored by all storage engines.

You'd have to enforce the XOR condition on the client side.