How can we emulate CHECK CONSTRAINT by using triggers?

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 car (number char(9));
Query OK, 0 rows affected (0.32 sec)

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

The above value is a valid one but what about the value which we are going to insert in the next query.

mysql> insert into car 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 BEFORE INSERT trigger to emulate CHECK CONSTRAINT for inserting the values −

Now, we can create a trigger as follows to prevent such kind of insertion −

mysql> delimiter //
mysql> create trigger car_insert_value before insert on car
    -> for each row
    -> begin
    -> if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    -> then
    -> signal sqlstate '45000' set message_text = 'Not a valid Number';
    -> end if;
    -> end //
Query OK, 0 rows affected (0.15 sec)

mysql> Delimiter ;
mysql> Delete from car;
Query OK, 2 rows affected (0.06 sec)

Now, when we will try to insert the incorrect number, the above-created trigger will stop us to do so and will throw an error as follows −

mysql> insert into car values('AB-2X5-YZ');
ERROR 1644 (45000): Not a Valid Number

But, we can insert the valid values as follows −

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

Creating BEFORE UPDATE trigger to emulate CHECK CONSTRAINT for updating the values −

Now, suppose if we will try to update the table with an incorrect value then MySQL is not going to stop us to do so as follows 7minus;

mysql> update car set number='AB-2X5-YZ';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Above query has updated the incorrect value in the table ‘car’. In the following query we are going to create a BEFORE UPDATE trigger which will prevent us to update incorrect data in the table −

mysql> delimiter //
mysql> create trigger car_update_value before update on car
    -> for each row
    -> begin
    -> if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    -> then
    -> signal sqlstate '45000' set message_text = 'Not a valid number';
    -> end if;
    -> end //
Query OK, 0 rows affected (0.11 sec)

mysql> Delimiter ;

mysql> update car set number='AB-2X5-YZ';
ERROR 1644 (45000): Not a valid number

Rama Giri

Published on 19-Feb-2018 06:22:48

• Related Questions & Answers
• How can we emulate CHECK CONSTRAINT by using views?
• How can we emulate CHECK CONSTRAINT by using MySQL GENERATED COLUMN?
• How VIEWS can be used to emulate CHECK CONSTRAINT?
• 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 check the list of all triggers in a database?
• How can we get the metadata of triggers?
• How can we use SIGNAL statement with MySQL triggers?
• How can we drop UNIQUE constraint from a MySQL table?
• How can we assign FOREIGN KEY constraint on multiple columns?
• How we can find all the triggers associated with a particular MySQL table?
• How can we see the information on triggers order in case of multiple triggers for same event and action time?
• How can we create a new database by using mysqladmin?
• How can we drop an existing database by using mysqladmin?