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.
