Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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'
Updated on: 22-Jun-2020
50 Views
Advertisements