MySQL and Check Constraints

constraintsmysql

I have inherited an application that uses MySQL and that is used by a PHP front end. The guy that wrote this system has gone to some fairly convoluted lengths to ensure that codes that users enter are valid – and tat means that these codes also exist in another table.

When I first saw this I wondered why he hadn't used CHECK constraints and let the dbms sort this out – I have visions of a load of different programs implementing the same checks instead of just the one place in the dbms. And then I found out that MySQL doesn't support Check constraints (not strictly true – it supports the syntax but just ignores it).

Is there a way that I can implement Check Constraints in MySQL?

Any hints, suggestions etc, would be great.

Best Solution

You can implement something similar to them with triggers, but MySQL itself doesn't support CHECK constraints. Don't worry though, it'll let you define them and just silently ignore them!