Good evening!

At some point I wanted to constrain values in a column called period in the my_parameters table so it would not be possible to set it higher than 7200. Simple task, isn’t it?

With MySQL, things are never that simple.

I was about to do it by simply calling:

ALTER TABLE my_parameters
ADD CHECK (period <= 7200);

It went through without errors. I tried to update the value of period so it would be higher than 7200, and it succeeded! Something wasn’t right.

I tried recreating the table:

CREATE TABLE (
-- ...
-- lots of stuff
-- ...
CONSTRAINT period_upper_limit CHECK (period <= 7200)
)

The query worked fine, but the subsequent updates or inserts with a high period value still worked fine.

There is actually a problem with MySQL mentioned here. CONSTRAINT and CHECK raise no errors in MySQL, but at the same time they are silently ignored. It was made for compatibility purposes. There is even a bug reported on this issue, but it’s over 14 years old and I’m afraid it’s unlikely that these instructions will ever be implemented.

Workaround? Workaround…

Using TRIGGER for such a task feels like bodging to me, and also overly complicated, but it’s the best I could come up with. Armed with instructions from here and here, I’ve made the triggers for this purpose.

So here’s what it looks like:

-- SET @upper_limit = 7200; -- not used, see the post scriptum

DELIMITER $$
CREATE TRIGGER check_period_insert BEFORE INSERT ON my_parameters FOR EACH ROW
BEGIN
    DECLARE baddata INT;
    declare msg varchar(128);
    SET baddata = 0;
    IF NEW.period > 7200 THEN
        SET baddata = 1;
    END IF;
    IF baddata = 1 THEN
        set msg = CONCAT('Cannot insert this because period ',NEW.period,' is too big!');
        signal sqlstate '45000' set message_text = msg;
    END IF;
END; $$
CREATE TRIGGER check_period_update BEFORE UPDATE ON my_parameters FOR EACH ROW
BEGIN
    DECLARE baddata INT;
    declare msg varchar(128);
    SET baddata = 0;
    IF NEW.period > 7200 THEN
        SET baddata = 1;
    END IF;
    IF baddata = 1 THEN
        set msg = CONCAT('Cannot update this because period ',NEW.period,' is too big!');
        signal sqlstate '45000' set message_text = msg;
    END IF;
END; $$
DELIMITER ;

All it does is checking of the value and emitting of an error signal if it is too big. The status 45000 for the signal basically means unhandled user-defined exception.

Conclusion

Things are not as simple as they might be sometimes, and even a powerful database like MySQL might not have a seemingly basic function. Luckily, workarounds are possible quite often.

Hope you found it helpful. I’ll see you eventually.

P.S. Initially I wanted to use @upper_limit variable so I would not have to repeat myself in code. The problem is that this variable exists only within the current session and the trigger receives the reference to it when it is created. In a new session it has the value of NULL; therefore, the trigger performs incorrectly.