MySQL: condition between two columns

Question

I have two columns:

• commission (percental('yes','no'))
• recurring(enum('yes','no').

I want to declare the following dependency:

recurring can only be 'yes', when percental is 'yes', too.

Is there a possibility to manage it within mysql?

Answer 1

you could create two triggers to check for this, sqlfiddle

CREATE
TRIGGER `before_insert` BEFORE INSERT 
    ON `commission` 
    FOR EACH ROW BEGIN
        IF NEW.recurring = 'yes' AND NEW.percental != 'yes' THEN
          signal sqlstate '45000' set message_text = "percental must be 'yes' for recurring to be 'yes'";
        END IF;
    END/
CREATE
    TRIGGER `before_update` BEFORE UPDATE
    ON `commission` 
    FOR EACH ROW BEGIN
        IF NEW.recurring = 'yes' AND NEW.percental != 'yes' THEN
          signal sqlstate '45000' set message_text = "percental must be 'yes' for recurring to be 'yes'";
        END IF;
    END/

since the code is the same for UPDATE and INSERT you might want to create a procedure to call for both triggers like this sqlfiddle

DROP PROCEDURE IF EXISTS check_commission_recurring_based_on_percental/

CREATE PROCEDURE check_commission_recurring_based_on_percental(IN percental ENUM('yes','no'), IN recurring ENUM('yes','no'))
BEGIN
  IF recurring = 'yes' AND percental != 'yes' THEN
     signal sqlstate '45000' set message_text = "percental must be 'yes' for recurring to be 'yes'";
  END IF;
END/
CREATE
    TRIGGER `before_insert` BEFORE INSERT
    ON `commission` 
    FOR EACH ROW BEGIN
        CALL check_commission_recurring_based_on_percental(NEW.percental,NEW.recurring);
    END/
CREATE
    TRIGGER `before_update` BEFORE UPDATE
    ON `commission` 
    FOR EACH ROW BEGIN
        CALL check_commission_recurring_based_on_percental(NEW.percental,NEW.recurring);
    END/