CODEWITHSUNDEEP
mysql
Raffael
Raffael

Reputation: 20045

how to enforce a constraint in mysql?

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?

Upvotes: 1

Views: 110

Answers (1)

Alain Collins
Alain Collins

Reputation: 16362

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

Upvotes: 2

Related Questions