CHECK constraint for MySQL – NOT NULL on generated columns

存储架构 2016-04-04

During our recent TechTour event the idea came up to implement JSON document validation not necessarily via foreign keys (as I have shown here
) but to define the generated column as NOT NULL. The generation expression must be defined in a way that it returns NULL for invalid data.

DISCLAIMER: This has already been explored by yoku0825 in his blogpost
. He deserves all credit!

Let's do a short test:

mysql> CREATE TABLE checker (

i int,

i_must_be_between_7_and_12 BOOLEAN

AS (IF(i BETWEEN 7 AND 12, true, NULL))

VIRTUAL NOT NULL);

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO checker (i) VALUES (11);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO checker (i) VALUES (12);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO checker (i) VALUES (13);

As you can see I used the column name to create a meaningful error message when inserting invalid data. It is perfectly possible to add a generated validation column for each data column so that you run several check constraints.

Or you can even check a combination of columns:

mysql> CREATE TABLE squares (

dx DOUBLE,

dy DOUBLE,

area_must_be_larger_than_10 BOOLEAN

AS (IF(dx*dy>10.0,true,NULL)) NOT NULL);

Query OK, 0 rows affected (0.05 sec)

Query OK, 1 row affected (0.01 sec)

ERROR 1048 (23000): Column 'area_must_be_larger_than_10' cannot be null

As generated columns are virtual by default this costs no extra storage. Data volume is the same. The expression is evaluated when inserting or updating data.

If you add a validation column to an already existing table and want to verify all existing rows, you could define the validation column as STORED (instead of the default VIRTUAL). This will fail if there are any invalid rows in your existing data set. However in normal operation a virtual column seems more appropriate for performance reasons. So I recommend to always use VIRTUAL validation columns and check pre-existing data separately with a small procedure.

责编内容by:Mablomy (源链)。感谢您的支持!

您可能感兴趣的

How to Submit MySQL Marinate Homework We will be submitting the homework via GitHub. The reason for this is to have a ...
理解MySQL的四种事务隔离级别 转载自: 《MySQL的四种事务隔离级别》 一、事务的基本要素(ACID) 原子性(Atomicity):事务开始后所有操作,要么全部做完,...
Pushing predicates into MySQL subqueries I am just getting started looking at MySQL performance tuning but I thought I wo...
我们是如何让一千个MySQL实例跑在Docker平台上的... 作者介绍 王晓波,同程旅游(LY.com)首席架构师,EGO 会员。专注于高并发互联网架构设计、分布式电子商务交易平台设计、大数据分析平台设计、高可用性...