- - - By CrazyStat - - -

14. August 2012

MySQL: How to do a (simple/complex) CHECK CONSTRAINT?

Filed under: DBMS — Tags: , , , , , , , , , — Christopher Kramer @ 02:27

Another “badly-missing-feature”-post. I should create a category for that I guess…

Usually, you can add a constraint to a table in SQL with something like:

ALTER TABLE foo ADD CONSTRAINT my_constraint CHECK (bla<>0)

Some sites like w3school even tell you this is the syntax for it in MySQL. Well, I guess they have never tried this and not even read the manual. Well, you can execute it in MySQL without an error. But also without any effect whatsoever. I tried it in InnoDB (MySQL 5.5) and it did not check the constraint at all.

The MySQL manual says:

The CHECK clause is parsed but ignored by all storage engines

Hallelujah, MySQL is great. A bug request for that exists since 2004, so don’t expect it to be fixed soon…

So we go with a trigger instead. For constraints, we use a BEFORE trigger. So next thing I tried looked something like this:

CREATE TRIGGER check_my_constraint BEFORE UPDATE ON `foo`
     IF (NEW.bla=0)
     END IF; 

Should work, but doesn’t. Problem is that MySQL doesn’t like ROLLBACK here. Hallelujah, MySQL is great. So what worked in the end is sending an error signal:

CREATE TRIGGER check_my_constraint BEFORE UPDATE ON `foo`
     DECLARE msg varchar(255);
     IF (NEW.bla=0)
        SET msg = concat('Constraint my_constraint violated: bla must not be zero ', cast(new.bla as char));
        SIGNAL sqlstate '45000' SET message_text = msg;
     END IF; 

Thanks to Rui Da-Costa for the solution with the SIGNAL.

The cool thing about triggers is that you can do complex checks here. So I just used it for something like:

     NEW.ref2_id<>(SELECT `ref2_id` FROM `ref` WHERE `ref`.id=NEW.`ref_id`))

So that makes sure that ref_id is either null or it refers to a row in table `ref` with this id that has the same ref2_id as the table we are checking. So a more complex type of foreign key relationship.

Hope this helped somebody to get things solved more quickly. Don’t forget to add an INSERT trigger as well.

Another remark: Make sure you do not use semicolon as delimiter when using SQL/PL syntax. phpMyAdmin has an extra field to change the delimiter (below the textarea where you enter your SQL) for example.


Try my Open Source PHP visitor analytics script CrazyStat.


  1. You help me! I will not waste my time to look for a solution, using the keyword “CHECK”, that doesn’t exist.. 😉

    Comment by s4ndr0 — 3. May 2013 @ 13:29

  2. ¡¡Thanks a lot!! :D, this was the solution that I was looking for. 😀 😀
    But I have a little correction: when you say CHECK (bla0) you are trying to make that bla be different of 0, but in the trigger you make the opposite verification, bla has to be 0 to be updated. Maybe the IF sentence have to be IF (!(NEW.bla0)), don’t you think? 😉
    Again thanks a lot, you’re awesome

    Comment by Di4n4 — 17. June 2013 @ 14:13

  3. @ Di4n4: Yeah, you’re right. In the example, it should be
    IF (NEW.bla=0)

    Comment by Christopher Kramer — 18. June 2013 @ 00:48

  4. […] Deletes oder Updates ausgelöst werden. Mit Triggern ist es beispielsweise möglich komplexe Tabellenconstraints in MySQL zu realisieren, die wesentlich mächtiger als die herkömmlichen MySQL-Constraints […]

    Pingback by Die Macht von SQL-Triggern › NETWAYS Blog — 21. August 2013 @ 16:12

  5. Thank you

    Comment by Raffaele — 19. October 2014 @ 19:51

  6. Thank you very much, I have been struggling with this for a lot of time!!

    Comment by Attila — 27. October 2014 @ 13:26

  7. […] or something like that) before attempting to perform the database manipulation you can fix it by (manually) add triggers to your tables to actually prevent a faulty […]

    Pingback by MySQL behaviour you should be aware of | — 15. January 2015 @ 04:38

  8. Great post… thanks for the help. Oh and I also lol’ed at you calling out W3 school

    Comment by Dakota — 20. February 2015 @ 20:13

  9. Hi i have to add a check constraint checking that DOB > 1947-01-01
    But how to write the trigger for this?

    Comment by Shatakshi — 9. April 2016 @ 08:28

RSS feed for comments on this post. TrackBack URL

Leave a comment