Take the 2-minute tour ×

I want to use the CHECK constraint in MySQL, but it is not implemented.

I have seen some workarounds with triggers. But they tend to set a default value to the field in question instead of returning an error.

Is it possible to construct a trigger the returns an error if a condition is not met?

Ultimately I want a trigger that copies a CHECK constraint.

share|improve this question
What exactly is your CHECK constraint? One specific type of Check constraints can be emulated by Foreign Key constraints. –  ypercube Mar 16 '12 at 9:55
it is possible to get a trigger to throw and error if the FK constraint is not what you are after. Have a look at this: stackoverflow.com/questions/24/throw-an-error-in-mysql-trigger –  Tom Mac Mar 16 '12 at 9:56
To @ypercube: could be something like CREATE TABLE test ( id INT PRIMARY KEY, number INT, CHECK (number > 0) ); –  smnsvane Mar 29 '12 at 11:36
Well, if you make that number UNSIGNED INT, only 0 and positive values will be allowed. Is that good enough? –  ypercube Mar 29 '12 at 12:21
No I also need to check other variables than numbers, but it will some of my problems, thx –  smnsvane Apr 2 '12 at 8:17
add comment

3 Answers

up vote 3 down vote accepted

Try the following syntax

CREATE TRIGGER mytabletriggerexample
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
share|improve this answer
add comment

From MySQL 5.5 onwards, you can use the SIGNAL syntax to return errors from stored procedures such as triggers.

share|improve this answer
add comment

Following what @Naltharial has pointed out, I wrote a procedure and a function to mimic a check constraint in a trigger. The details, along with a rewrite of @naveen-kumar's example, are in this answer.

share|improve this answer
add comment

Your Answer


