I previously wrote about using SQL check constraints in MySQL. It seems however that the MySQL relational database management system (RDBMS) does not actually support the fundamental verification used by check constraints in other RDBMSs. This means that although you can add check constraints to tables without issue, they are not validated and data is inserted or updated.
The latest MySQL documentation (5.5) discretly mentions the fact these constraints are unfortunately not supported.
- The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.
- The UNIQUE and PRIMARY KEY information is about the same as what you get from the Key_name field in the output from SHOW INDEX when the Non_unique field is 0.
- The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. This is a CHAR (not ENUM) column. The CHECK value is not available until we support CHECK.
Take special note of the final line I’ve emboldened. This is quite a shame that this constraint type is not fully supported yet.
Apologies to readers for the implication that this was functional in my previous post, and thanks to the poster of this comment for noticing the discrepancy.
Check constraints, of course, function fine in many other DBMSs, and to that end, the original post on SQL check constraints is still relevant, but sadly not if you are worked with MySQL.
If you liked this post, you may be interested in these:
Ya, I’m so upset today.
poor mysql
I just tried to make check constraint in mysql db… its not working…
There are workarounds. For example you can simulate CHECK with view. Check this out:
http://www.cphpvb.net/db/5671-%D1%81%D0%B8%D0%BC%D1%83%D0%BB%D0%B8%D1%80%D0%B0%D0%BD%D0%B5-%D0%BD%D0%B0-check-%D1%81-view/
Sorry, it’s in Bulgarian language, but the code is code in any language, right?
Good one, Philip. I can’t believe how many ‘Tutorials’ for MySQL publish all these examples of how to use check constraints when they don’t really work! I was going crazy looking for the reason, because the same code worked fine on IBM’s DB2 for the [AS/400, System i, iSeries, IBM i - pick a name] which is what I normally work on.
Well if you like workarounds, I’d suggest a set of before triggers as these are more likely to emulate a check efficiently
As the guy said in his article … the flaws I pointed out in MySQL invalidate this.
http://bugs.mysql.com/bug.php?id=62209&thanks=2¬ify=67
MySQL is not ACID compliant — check the original article for details on the trigger implementation however.
It’s incredible that they don’t mention it even at w3schools:
http://www.w3schools.com/Sql/sql_check.asp
they just list mysql as one of the options there…
Hmm, but you don’t need check constraints in MySQL, you have the enum data type, just make sure you have sql_mode = ‘STRICT_ALL_TABLES’ when you create your table and it works just fine.
mysql> set sql_mode=’STRICT_ALL_TABLES’;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test (col1 enum (‘good’,'bad’));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values (‘good’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (‘bad’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (‘not allowed’);
ERROR 1265 (01000): Data truncated for column ‘col1′ at row 1
mysql> select * from test;
+——+
| col1 |
+——+
| good |
| bad |
+——+
2 rows in set (0.00 sec)
http://tinky2jed.wordpress.com/oracle-stuff/mysql-check-constraint-equivalent/
Not a solution at all. How you will make with enum type “values of int higher than 20 and smaller than 500″? You will make an enum type with 480 entries? I doubt that
What about “salary higher than 0″? You will count infinite amount of numbers in the enum type? I doubt that again 