How can we emulate CHECK CONSTRAINT by using MySQL GENERATED COLUMN?

As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −

Example

Suppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −

mysql> Create table car2 (number char(9));
Query OK, 0 rows affected (0.32 sec)

mysql> Insert into car2 values('AB-235-YZ');
Query OK, 1 row affected (0.10 sec)

The above value is a valid one but what about the value that we are going to insert in the next query.

mysql> insert into car2 values('AB-2X5-YZ');
Query OK, 1 row affected (0.04 sec)

The above value is not a valid one because it contains a character between digits which is against the fixed syntax we are using.

Using GENERATED COLUMN to emulate CHECK CONSTRAINT for inserting and updating the values −

mysql> Alter table car2 add column number_validate char(0) as (case when number
rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
  -> then '' end)
  -> virtual not null;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> Describe car2;
+-----------------+---------+------+-----+---------+-------------------+
| Field           | Type    | Null | Key | Default | Extra             |
+-----------------+---------+------+-----+---------+-------------------+
| number          | char(9) | YES  |     | NULL    |                   |
| number_validate | char(0) | NO   |     | NULL    | VIRTUAL GENERATED |
+-----------------+---------+------+-----+---------+-------------------+
2 rows in set (0.00 sec)

mysql> Insert into car2 (number) values('AB-235-YZ');
Query OK, 1 row affected (0.09 sec)

The above query inserts the valid number hence it is accepted. But the below query is not inserting as well as updating the valid number.

mysql> Insert into car2 (number) values('AB-2X5-YZ');
ERROR 1048 (23000): Column 'number_validate' cannot be null

mysql> Update car2 set number='AB-2X5-YZ';
ERROR 1048 (23000): Column 'number_validate' cannot be null