Check Constraints in MySQL 5.0?

First let me point out MySQL doesn't support check constraints, I just wanted to point that out up front before you run off looking for them. When I was writing content on Triggers for my site www.mysqldevelopment.com, I was looking at ways to use triggers in the real world. People were asking me why they might want to use them. I came up with a few but one I mentioned just didn't seem to work. That was implementing check constraints via triggers. A check constraint is a constraint on a column in a table which limits what the column will accept, Oracle for example allows the use of check constraints when creating a table. MySQL itself has constraints such as primary key and unique but the features are very much limited to what MySQL wants you to do. A check constraint allows more flexibility, you can dictate what you want to allow in the column.

For me it was obvious that triggers would be a great place to implement such a thing but the more I looked the less happy I was about implementing them. The first reason was that prior to 5.0.10 you couldn't access SQL from within a trigger. One of the things I would have needed to do was raise an error in the trigger to stop processing the insert/update, MySQL SQL/PSM doesn't currently allow you to raise an error manually, one way to do that would be to artificially raise one but because of the lack of support for SQL in triggers this wasn't possible without doing some potentially damaging things to the database.

However come release 5.0.10 I still wasn't happy, the sort of error messages we could raise didn't relate to the actual error at all. So I gave up on the idea in the short term.

But then I got an email from Scott Maxwell who came up with a suggestion on how this might be done. He's written a short article on the subject which can be seen here....

Emulating Check Constraints using Triggers.