how to enforce a constraint in mysql?

Question

I have a table T with two fields A and B of type integer.

Now I want to enforce that A is smaller or equal to B.

Do I have to create a "before insert/update"-trigger?

And if yes then how do I make the trigger fail?

score 2 · Accepted Answer · answered Jun 02 '11 at 23:14

2

You could do it in your application code, but it's safest to do it in the database via a trigger.

To prevent the insert from succeeding, generate an error in your trigger. We do something like this:

CREATE TRIGGER `t_insert`
BEFORE UPDATE ON `t`
FOR EACH ROW
  BEGIN
    IF new.A > new.B THEN
      CALL NONEXISTENT_PROC()
    END IF;
  END

answered Jun 02 '11 at 23:14

Alain Collins

is this ugly or what? ;-) how do you know what failed, as there might be several triggers? would I have to name the procedure greatness_constraint_failed_on_A_and_B_in_T() ... my goodness... – Raffael Jun 03 '11 at 08:19
Never said it was pretty, nor even the best way. We only use it in one spot, and that's for people who try to update/delete the audit log table that we have. How about this - do everything through a stored procedure. I have another site that runs like that and I love it. – Alain Collins Jun 08 '11 at 21:39
I am just wondering b/c this seems to be a very naturally useful feature and if yours is the most straightforward solution then MySQL would be lacking it seriously. – Raffael Jun 09 '11 at 09:39

1 Answers1