mpopp.net blog – Markus Popp

Check constraints are one of the features that MySQL’s still missing (unfortunately), so I tried if there’s a workaround using triggers.

My idea was quite simple. I created one AFTER INSERT trigger and one AFTER UPDATE trigger. They should verify, if they meet the conditions and if they do not, the AFTER INSERT should delete the just inserted record again and the AFTER UPDATE trigger should update the values back to their previous values.

So I created this table:

mysql> CREATE TABLE check_test (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(200) NOT NULL,
    -> email VARCHAR(200) NOT NULL,
    -> score INT NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

The email address should be verified with the regular expression ‘^[a-z0-9_\.-]+@[a-z0-9_-]+\.[a-z0-9_\.-]+$’ and the score should be between 0 and 100.

So I implemented the following two triggers:

mysql> DELIMITER //
mysql>
mysql> DROP TRIGGER check_test_ai//
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE TRIGGER check_test_ai
    ->   AFTER INSERT ON check_test
    ->   FOR EACH ROW
    -> BEGIN
    ->
    ->   IF new.email NOT REGEXP 
    ->     '^[a-z0-9_\.-]+@[a-z0-9_-]+\.[a-z0-9_\.-]+$'
    ->     OR new.score  100 THEN
    ->
    ->     DELETE FROM check_test WHERE id = new.id;
    ->   END IF;
    ->
    -> END//
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TRIGGER check_test_au//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TRIGGER check_test_au
    ->   AFTER UPDATE ON check_test
    ->   FOR EACH ROW
    -> BEGIN
    ->
    ->   IF new.email NOT REGEXP 
    ->     '^[a-z0-9_\.-]+@[a-z0-9_-]+\.[a-z0-9_\.-]+$'
    ->     OR new.score  100 THEN
    ->
    ->     UPDATE check_test SET id = old.id, name = old.name, 
    ->         email = old.email, score = old.email
    ->       WHERE id = new.id;
    ->   END IF;
    ->
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

Let’s see how they work and let’s first insert a valid record:

mysql> INSERT INTO check_test
    ->   (name, email, score)
    ->   VALUES
    ->   ('Person1', 'person1@email.com', 88);
Query OK, 1 row affected (0.00 sec)

… and now two invalid records – one with an invalid email address and one with an invalid score:

mysql> INSERT INTO check_test
    ->   (name, email, score)
    ->   VALUES
    ->   ('Person2', 'aninvalidemailaddress', 45);
ERROR 1442 (HY000): Can't update table 'check_test' in stored 
function/trigger because it is already used by statement 
which invoked this stored function/trigger.
mysql> INSERT INTO check_test
    ->   (name, email, score)
    ->   VALUES
    ->   ('Person3', 'person3@email.com', 122);
ERROR 1442 (HY000): Can't update table 'check_test' in stored 
function/trigger because it is already used by statement 
which invoked this stored function/trigger.

Whooops – we get an error message. It says that the table can’t be updated, because the table is already in use. So it seems, a trigger can’t refer to its own table. But lets see what the content of the table is:

mysql> SELECT * FROM check_test;
+----+---------+-------------------+-------+
| id | name    | email             | score |
+----+---------+-------------------+-------+
|  1 | Person1 | person1@email.com |    88 |
+----+---------+-------------------+-------+
1 row in set (0.00 sec)

However – the result is what I wanted. The trigger refused to insert the records with invalid values.

So let’s try updates (one valid and one invalid):

mysql> UPDATE check_test
    ->   SET email = 'anotheremail@email.com'
    ->   WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE check_test
    ->   SET score = 200
    ->   WHERE id = 1;
ERROR 1442 (HY000): Can't update table 'check_test' in stored 
function/trigger because it is already used by statement 
which invoked this stored function/trigger.

Generally, it’s the same result. What’s the content of the table now?

mysql> SELECT * FROM check_test;
+----+---------+------------------------+-------+
| id | name    | email                  | score |
+----+---------+------------------------+-------+
|  1 | Person1 | anotheremail@email.com |    88 |
+----+---------+------------------------+-------+
1 row in set (0.00 sec)

Again, the result is what we intended to do. MySQL refused to change the record to an invalid value, but did the correct update.

So it’s of course not the elegant way, because I initially planned to properly delete the invalid records again or update them back to their original values. Instead, MySQL now throws an error and interrupts the INSERT or UPDATE, so the values still remain on their original state (or are refused from being updated).

However, the much more elegant way would be proper check constraints, but I’m confident that there’s light at the end of the tunnel.

If somebody has more elegant solutions to this issue – please let me and all MySQL interested people know :-).