CODEWITHSUNDEEP
mysqlsqldatabase
Hossein
Hossein

Reputation: 41821

How can I define conditional non-null constraints on several columns in mySql?

I have a table in my database. This table contains several columns say A,B,C,D,E,F and G. I want to define a constraint in a way that at least one of D,E or F should not be null. Is this possible? I am using MySql. Thanks

Upvotes: 3

Views: 889

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

Unfortunately, MySQL does not support CHECK constraints. It parses them and then silently discards the constraint, just like it does for foreign key constraints on a MyISAM table. It doesn't even give you a warning about the unsupported constraint type, which I think is a bad design decision on their part.

Here's a solution using a trigger:

mysql> DELIMITER //
mysql> CREATE TRIGGER check_one_not_null BEFORE INSERT ON mytable FOR EACH ROW 
    IF COALESCE(NEW.D, NEW.E, NEW.F) IS NULL 
    THEN SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'One of D, E, or F must have a non-null value.';
    END IF //

You should also create a similar trigger BEFORE UPDATE on the same table.

See http://dev.mysql.com/doc/refman/5.6/en/signal.html for more information on the SIGNAL statement to raise exceptions in MySQL triggers or stored routines.

Upvotes: 2

Andy Lester
Andy Lester

Reputation: 93636

Note: It turns out that this answer doesn't apply to MySQL, because MySQL doesn't check constraints. It lets you pretend that you have constraints, but does nothing to enforce them. Oops.

You can't do NOT NULL conditionally but you can define constraints that apply to certain columns depending on other columns.

For instance, if you have an EMPLOYEE record that has PAYTYPE of "H" or "S" for hourly or salary, you can do 

CREATE TABLE EMPLOYEE (
    name VARCHAR2(100),
    paytype CHAR(1),
        CONSTRAINT paytype_check CHECK ( paytype IN ( 'H','S' ) ),
    hourly_rate NUMBER,
    salary NUMBER,
        CONSTRAINT salary_hourly_check CHECK (
            ( paytype='H' AND hourly_rate IS NOT NULL AND salary IS NULL )
            OR
            ( paytype='S' AND hourly_rate IS NULL AND salary IS NOT NULL )
        )
    );

In your case, you'll do something like

CONSTRAINT d_e_f_check CHECK (d IS NOT NULL OR e IS NOT NULL OR f IS NOT NULL)

Upvotes: 0

Related Questions