How to define a table in SQL with 2 fields being not-null (mandatory) dependent?

Question

Not-null dependent means that at least 1 field is not null.

Ideas?

Example: email and phone-number can be not-null dependent. If email is null, phone-number must be not-null. If phone-number is null, email must be not-null.

Answer 1

MySQL doesn't support CHECK constraints, despite this feature having been requested since 2004.

Annoyingly, it parses CHECK constraint syntax, but it doesn't store the constraint or enforce it later. I have thought for a long time that this is misleading to accept an SQL statement that the RDBMS has no support for, without reporting even a warning.

To solve your problem, you could use a trigger:

CREATE TRIGGER t BEFORE INSERT ON mytable
FOR EACH ROW BEGIN
    IF COALESCE(NEW.email, NEW.phone_number) IS NULL THEN
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Either email or phone_number must be non-null';
    END IF;
END

You also need to define a similar trigger before UPDATE.