jovoto development blog

Since MySQL does neither have real check constraints nor a way to raise an exception in a stored procedure, we found it not instantly obvious, how we could reject a certain row on insert, based on a certain condition.

A nice way we found was to set the value in question to NULL, based on the condition and let the NOT NULL constraint do its work.

ALTER TABLE some_table MODIFY some_id varchar(255) NOT NULL;
DROP TRIGGER IF EXISTS check_someid;
DELIMITER $$
CREATE TRIGGER check_someid BEFORE INSERT ON some_table  
FOR EACH ROW BEGIN
  IF NOT NEW.some_id REGEXP '^[[:xdigit:]]{32}$' THEN
    SET NEW.some_id = NULL;
  END IF;
END;
$$
DELIMITER ;

The trigger will let any 32 character string with only HEX characters for the column some_id pass and rejects the rest.

> INSERT INTO some_table (some_id) VALUES ('ffffffffffffffffffffffffffffffff');
Query OK, 1 row affected (0.01 sec)

> INSERT INTO some_table (some_id) VALUES ('fffffffffffffffffffffffffffffffg');
ERROR 1048 (23000): Column 'some_id' cannot be null

Happy triggering.