CODEWITHSUNDEEP
mysqlconstraints
GordonM
GordonM

Reputation: 31730

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

I want to have a SQL table with two columns. One is a key into another table, the other holds a string literal. The idea is phone numbers can be entered either exactly (in which case the ID into the phonebook table is used) or as a wildcard (in which the string literal is used).

This means that one column in the table will hold a value, and the other will hold a NULL.

Is it possible to constrain a table in such a way that one column must have a value, and the other must be NULL? If both columns are NULL or both have a value, then the row is invalid.

I have a feeling that MySQL can't do this (as it doesn't seem to have a comprehensive toolbox when it comes to constraints), but it couldn't hurt to ask.

Upvotes: 9

Views: 10986

Answers (5)

ePi272314
ePi272314

Reputation: 13437

Just one line of code

You can use the CHECK constraint to ensure one of the column is not null.

check (column1 is not null or column2 is not null)

Full example tested in MySQL:

create table Clients (
    id int primary key,
    name varchar(100) not null,
    email varchar(100),
    telephone varchar(20),
    constraint CHK_Clients_Email_Telephone check (email is not null or telephone is not null)
);

Upvotes: 1

Leukipp
Leukipp

Reputation: 590

The following triggers worked for me:

CREATE TRIGGER tgr_OrgFeeOwnerInsert
    BEFORE INSERT
    ON OrganisationFee
    FOR EACH ROW
BEGIN
    IF (SELECT ((new.fieldA IS NULL) + (new.fieldB IS NULL) + (new.fieldC IS NULL)) <> 2)
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'my error message';
    END IF;
END;

CREATE TRIGGER tgr_OrgFeeOwnerUpdate
    BEFORE UPDATE
    ON OrganisationFee
    FOR EACH ROW
BEGIN
    IF (SELECT ((new.fieldA IS NULL) + (new.fieldB IS NULL) + (new.fieldC IS NULL)) <> 2)
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'my error message';
    END IF;
END;

Upvotes: 0

Akxe
Akxe

Reputation: 11495

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.

Upvotes: 8

Drogos
Drogos

Reputation: 41

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

Upvotes: 3

titanoboa
titanoboa

Reputation: 2588

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.

Upvotes: 4

Related Questions