MySQL check constraint equivalent

Today I was working on a project and wanted to use a check constraint. Ultimately, it would be good to have a reference table, but I wanted something quick like a check constraint. MySQL complained when I tried to create it, so I went looking. I found a few pages mentioning this problem, but none with a solution. Fortunately it didn’t take long before I remembered reading that MySQL has an enumeration (enum) data type.

mysql> create table test1 (col1 varchar(10), check col1 in ('This','That'));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax
to use near 'col1 in ('This','That'))' at line 1

So, I tried it with enum data type.

mysql> create table test1 (col1 enum ('This','That'));
Query OK, 0 rows affected (0.22 sec)

mysql> insert into test1 values ('This');
Query OK, 1 row affected (0.26 sec)

mysql> insert into test1 values ('That');
Query OK, 1 row affected (0.22 sec)

mysql> select * from test1;
+------+
| col1 |
+------+
| This |
| That |
+------+
2 rows in set (0.00 sec)

So far so good, but then I tried:

mysql> insert into test1 values ('TheOther');
 Query OK, 1 row affected, 1 warning (0.21 sec)
mysql> select * from test1;
 +------+
 | col1 |
 +------+
 | This |
 | That |
 |      |
 +------+
 3 rows in set (0.00 sec)

Whoops, not so good (note the third row with null value). Due to MySQL implicit datatype conversion, it took the invalid value and made it something that worked – NULL. It is always a good idea to create tables using Strict SQL Mode. Strict SQL mode prevents implicit data type conversion, but it must be set when the table is created. This would prevent this type of issue, as below:

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2 (col1 enum('This','That'));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test2 values ('This');
Query OK, 1 row affected (0.28 sec)

mysql> insert into test2 values ('That');
Query OK, 1 row affected (0.20 sec)

mysql> insert into test2 values ('TheOther');
ERROR 1265 (01000): Data truncated for column 'col1' at row 1

mysql> select * from test2;
+------+
| col1 |
+------+
| This |
| That |
+------+
2 rows in set (0.01 sec)

Well, it isn’t the most useful error message, but the bad data is avoided.

This solution should work fine in most cases; however, if you need to check against a “greater than” or “less than” you aren’t going to be able to use it. In those cases, you will probably want to look into using a trigger.

Advertisements

2 responses to “MySQL check constraint equivalent

  1. Pingback: Can you do check constraints in MySQL | Jed's

  2. thanks for sharing such a helpful technique.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s