How to use VIEWS to simulate CHECK CONSTRAINT?

As we all know, MySQL supports foreign keys for referential integrity, but does not support CHECK constraints. But we can simulate them by using triggers. This problem can be fixed with the help of the example given below -

Example

Suppose we have a table named "car1" The syntax registration number is like two letters, one dash , three numbers, a dash, two letters, as follows -

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

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

The above value is valid, but what about the value we want to insert in the next query?

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

The above value is not a valid value because it contains a character between numbers, which violates the fixed syntax we use.

Create VIEW to simulate CHECK CONSTRAINT to insert and update values ​​-

mysql> Create view car_invalid_check as
    -> Select * from car1 WHERE number rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    -> with check option;
Query OK, 0 rows affected (0.12 sec)

mysql> Insert into car_invalid_check values('AB-2X5-YZ');
ERROR 1369 (HY000): CHECK OPTION failed 'query.car_invalid_check'

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

mysql> Update car_invalid_check SET NUMBER = 'AB-2X5-ZT';
ERROR 1369 (HY000): CHECK OPTION failed 'query.car_invalid_check'

The above is the detailed content of How to use VIEWS to simulate CHECK CONSTRAINT?. For more information, please follow other related articles on the PHP Chinese website!