Made with Gila CMS

Mysql: ensure than one of two columns has a value

Posted on December 9, 2019

 You have two columns that accept new values, but at least one of them must not be null:

CREATE TRIGGER tr_Col1Col2 BEFORE INSERT ON myTable
FOR EACH ROW BEGIN
  IF (NEW.Col1 IS NULL AND NEW.Col2 IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'Col1' or \'Col2\' must have a value';
  END IF;
END