An idea for using MySQL 5.7's generated column like CHECK constraint

This is translation for my early post in Japanese.

As of MySQL 5.7.6, generated column was introduced as 5.7's new feature.
I found that generated column could behave like CHECK constraint.
Let's start :)

mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------------+
| num | val        |
+-----+------------+
|   1 | 2015-08-06 |
+-----+------------+
1 row in set (0.00 sec)

First, there's the bad designed table which has DATE-string in its VARCHAR column.

mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Second, add VIRTUAL generated column `check_val` which calculates DATETIME from `val`'s DATE-string.

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-09-31'

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
+-----+------------+---------------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
+-----+------------+---------------------+
2 rows in set (0.00 sec)

After that, you can see INSERT statement with incorrect DATE-string fails because of `check_val`'s CAST function raises warning and strict sql_mode can't allow that warning.

mysql57> SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql57> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2015-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
|   3 | 2015-11-31 | NULL                |
+-----+------------+---------------------+
3 rows in set, 1 warning (0.00 sec)

This way depends on sql_mode is strict or not.
Then I tried another way using IF function, which returns NULL when check condition is false, with NOT NULL constraint.

mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (1, 'Tokyo');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (2, 'Osaka');
Query OK, 1 row affected (0.01 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (3, 'Nara');
ERROR 1048 (23000): Column 'check_val' cannot be null

mysql57> SELECT * FROM t2;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
+-----+-------+-----------+
2 rows in set (0.00 sec)

I seem this approach is better.
NOT NULL constraint doesn't depend on sql_mode.

This generated column feature can be introduced into tables which have already been filled data.

mysql57> SELECT * FROM t3; -- Invalid data has already been inserted.
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL; -- Add VIRTUAL generated column.
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
|   3 | Nara  |         0 |
+-----+-------+-----------+
3 rows in set (0.00 sec)

mysql57> INSERT INTO t3 (num, val) VALUES (4, 'Kyoto');
ERROR 1048 (23000): Column 'check_val' cannot be null

With VIRTUAL generated column, the data which is already stored don't be affected by fake CHECK constraint.

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers.

http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

VIRTUAL generated `check_val` doesn't calculate at the time of ALTER TABLE, thus this case doesn't affect stored data but new data are under constraint.

mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null

The other hand, with STORED generated column, all exist data are calculated and checked during ALTER TABLE.
If the table already has invalid (for fake CHECK constraint) data, ALTER TABLE fails.

This is a stage of idea, is not practice in production environment.
But this idea maybe satisfy some case of "OMG, doesn't MySQL have CHECK constraint!?" situation.