DeutschEnglish

Submenu

 - - - 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`
     FOR EACH ROW 
     BEGIN 
     IF (NEW.bla=0)
     THEN 
     ROLLBACK;
     END IF; 
     END

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`
     FOR EACH ROW 
     BEGIN 
     DECLARE msg varchar(255);
     IF (NEW.bla=0)
     THEN 
        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; 
     END

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:

 IF (NEW.ref_id IS NOT NULL AND
     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.

Recommendation

Try my Open Source PHP visitor analytics script CrazyStat.

9 Comments »

  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 | Bram.us — 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