MySQL: Constraining a set of columns so at least one is not NULL

Solution 1:

I am not aware of a way to enforce such a constraint.

As a workaround, you may consider to have two different columns: If you have one column for the data - containing the phonebook id or the string literal, and another column for the data type - either 'exact' or 'wildcard' -, you can set a NOT NULL constraint to both columns. One obvious drawback is that you cannot have a FK constraint to the phonebooks table any more.

Solution 2:

You can make triggers to run on before the insert, to check the values and determine if the insert or update should happen or not. A good example for how to create triggers like this can be found here: https://dba.stackexchange.com/questions/43284/two-nullable-columns-one-required-to-have-value

Solution 3:

Ever since GENERATED columns are a thing, this is possible.

CREATE TABLE `test_multiple_not_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `idOne` int(11) DEFAULT NULL,
  `idTwo` int(11) DEFAULT NULL,
  `not_null_constrain` int(11) GENERATED ALWAYS AS (coalesce(`idOne`,`idTwo`)) VIRTUAL NOT NULL,
  PRIMARY KEY (`id`)
);

Since every time a row is inserted, the generated column must run to see it if satisfies NOT NULL constrain it will reply with 1048: Column 'not_null_constrain' cannot be null, if it would violate this restriction.