Can a MySQL trigger simulate a CHECK constraint?

Question

I want to use the CHECK constraint in MySQL, but it is not supported. (Unlike other RDBMS, it will understand but not enforce the CHECKs.)

I have seen some workarounds with triggers. But they tend to set a default value to the field in question instead of returning an error.

Is it possible to construct a trigger that returns an error if a condition is not met?

Ultimately I want a trigger that copies a CHECK constraint.

What exactly is your `CHECK` constraint? One specific type of Check constraints can be emulated by Foreign Key constraints. — ypercubeᵀᴹ, Mar 16 '12 at 09:55
it is possible to get a trigger to throw and error if the FK constraint is not what you are after. Have a look at this: http://stackoverflow.com/questions/24/throw-an-error-in-mysql-trigger — Tom Mac, Mar 16 '12 at 09:56
To @ypercube: could be something like CREATE TABLE test ( id INT PRIMARY KEY, number INT, CHECK (number > 0) ); — smnsvane, Mar 29 '12 at 11:36
Well, if you make that `number UNSIGNED INT`, only 0 and positive values will be allowed. Is that good enough? — ypercubeᵀᴹ, Mar 29 '12 at 12:21
No I also need to check other variables than numbers, but it will some of my problems, thx — smnsvane, Apr 02 '12 at 08:17

score 16 · Answer 1 · answered Aug 12 '14 at 14:06

when you are updating data :

delimiter $$
create trigger chk_stats1 before update on stats 
  for each row 
   begin  
    if  new.month>12 then
        SIGNAL SQLSTATE '45000'   
        SET MESSAGE_TEXT = 'Cannot add or update row: only';
      end if; 
      end; 
      $$

when you are inserting data :

   delimiter $$
    create trigger chk_stats before insert on stats 
      for each row 
       begin  
      if  new.month>12 then
       SIGNAL SQLSTATE '45000'   
       SET MESSAGE_TEXT = 'Cannot add or update row: only';
       end if; 
    end; 
    $$

these trigger will work as check constraint ,work before insert or update and check month, if month >12 gives error .

Also after executing those statements, you should execute `delimiter ;` to set the delimiter back to `;`. — steoiatsl, Sep 22 '21 at 22:47

score 7 · Answer 2 · answered Mar 16 '12 at 09:56

7

From MySQL 5.5 onwards, you can use the SIGNAL syntax to return errors from stored procedures such as triggers.

answered Mar 16 '12 at 09:56

Naltharial

2,132
14
21

score 3 · Accepted Answer · answered Mar 16 '12 at 12:30

Try the following syntax

CREATE TRIGGER mytabletriggerexample
BEFORE INSERT
FOR EACH ROW BEGIN
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF; END;

Can a MySQL trigger simulate a CHECK constraint?

3 Answers3

Linked