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 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
59 Views
