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:
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:
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.
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:
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.
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.