How VIEWS can be used to emulate CHECK CONSTRAINT?

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'

Fendadis John

Updated on 22-Jun-2020 14:16:24

• Related Questions & Answers
• How can we emulate CHECK CONSTRAINT by using 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 emulate CHECK CONSTRAINT by using triggers?
• How can we emulate CHECK CONSTRAINT by using MySQL GENERATED COLUMN?
• How can Tensorflow be used to check the predicrion using Python?
• How can Tensorflow be used to check the predictions using Python?
• How can Tensorflow be used to export the model so that it can be used later?
• How can Keras be used to recreate the model and check its accuracy?
• Where MySQL views can be inconsistent and how can we ensure their consistency?
• How can Tensorflow be used to implement custom layers?
• How can we create MySQL views?
• How can Keras be used to implement ensembling in Python?
• How can matplotlib be used to create histograms using Python?
• How can Matplotlib be used to generate time-series data?