MySQL best way to define a couple of columns as both null or both not-null

Question

On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns are not null. Is there any specific constraint, or could I do it just via BEFORE INSERT / BEFORE UPDATE triggers?

In other words, if a column of a record is null and it is updated setting it with a value, also the other column of that record must be updated with a not-null value. If the column is not null already, I can update it with another value (optionally, the other one too), but if I set it as null, the other column too must be set as null by the update query (no "default" settings).

Thank you.

Answer 1

I should think a table-level CHECK constraint would serve here, something like

CONSTRAINT c1_c2_null_match
  CHECK ((c1 IS NULL AND c2 IS NULL)
     OR  (c1 IS NOT NULL AND c2 IS NOT NULL))

See the MySQL 8.0 documentation.

You could instead use a trigger, but can run into consistency issues that way. Certain bulk-load operations will bypass triggers, meaning that the check is never performed at all and allowing the condition to be violated. A constraint can be temporarily disabled, but the check will be performed when the constraint is re-enabled.

One note: You don't mention which version of MySQL you're using, but prior to 8.0.16 CHECK constraints were ignored, so you would need to use a trigger to do what you want.

To do this with a trigger you need to return an error if the fields don't match, something like this:

DELIMITER //
CREATE TRIGGER c1_c2_null_match BEFORE UPDATE ON tbl
  FOR EACH ROW
  BEGIN
    IF (NEW.c1 IS NULL AND NEW.c2 IS NOT NULL)
    OR (NEW.c1 IS NOT NULL AND NEW.c2 IS NULL)
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Null columns must match';
    END IF;
  END //
DELIMITER;

See this Stack Overflow question.

Hope that helps.

Answer 2

Most database systems normally handle this with a Check Constraint, but your version of MySQL doesn't have that implemented. So you can implement it with a Trigger as discussed in this StackOverflow answer.