CODEWITHSUNDEEP
mysqlsqlcreate-tablecheck-constraints
Petaurus
Petaurus

Reputation: 21

MySQL: how to make an attribute "nullable" only if certain condition

I am creating a table for a DB, and I would like (if possible) to do something like this: Attribute X can be NULL if, and only if, attribute Y is "value1".

Is there a way to do this? I want to do this because I could delete an entity, reducing the complexity of my project (or at least I think I would get some advantages).

Thanks :)

Upvotes: 1

Views: 118

Answers (2)

Akina
Akina

Reputation: 42622

If MySQL version is not new enough for to use CHECK constraint (below 8.0.16) then use 

DELIMITER @@;

CREATE TRIGGER tr_bi_check_my_constraint
BEFORE INSERT
ON my_table
FOR EACH ROW
BEGIN
    IF NEW.attribute_X IS NULL AND NEW.attribute_Y != 'value1' THEN
        SIGNAL SQLSTATE 45000
            SET MESSAGE_TEXT = 'Attribute X can be NULL if, and only if, attribute Y is "value1".';
    END IF;
END
@@;

CREATE TRIGGER tr_bu_check_my_constraint
BEFORE UPDATE
ON my_table
FOR EACH ROW
BEGIN
    IF NEW.attribute_X IS NULL AND NEW.attribute_Y != 'value1' THEN
        SIGNAL SQLSTATE 45000
            SET MESSAGE_TEXT = 'Attribute X can be NULL if, and only if, attribute Y is "value1".';
    END IF;
END
@@;

DELIMITER ;

Upvotes: 1

GMB
GMB

Reputation: 222432

In very recent versions of MySQL, you can use a check constraint for this:

create table mytable (
    x int,
    y int,
    check(x is not null or y = 1)
)

Upvotes: 1

Related Questions