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.