How can we emulate CHECK CONSTRAINT by using views?

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 ‘car1’ which can have the fix syntax registration number like two letters, a dash, three digits, 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 a valid one but what about the value that we are going 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 one because it contains a character between digits which is against the fixed syntax we are using.

Creating VIEW to emulate CHECK CONSTRAINT for inserting and updating the 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'

Vikyath Ram

Published on 19-Feb-2018 06:39:28

• Related Questions & Answers
• How can we emulate CHECK CONSTRAINT by using triggers?
• How VIEWS can be used to emulate CHECK CONSTRAINT?
• How can we emulate CHECK CONSTRAINT by using MySQL GENERATED COLUMN?
• How can we create MySQL views?
• How BEFORE INSERT triggers can be used to emulate CHECK CONSTRAINT for inserting values in the table?
• How BEFORE UPDATE triggers can be used to emulate CHECK CONSTRAINT for updating values in the table?
• How can we check the indexes created by a UNIQUE constraint on a MySQL table?
• How can we create MySQL views with column list?
• How can we use logical operators while creating MySQL views?
• How can we create MySQL views without any column list?
• How can we drop UNIQUE constraint from a MySQL table?
• How can we assign FOREIGN KEY constraint on multiple columns?
• Where MySQL views can be inconsistent and how can we ensure their consistency?
• How can we create a new database by using mysqladmin?
• How can we drop an existing database by using mysqladmin?