mysql check constraint where either one of two columns should be null

Question

(Sorry if this is a duplicate post, but I can't seem to find an example for this problem)

I have a mysql table with 4 columns like this:

SomeTable
=========
ID (int)
name (varchar(255))
column1 (varchar(255))
column2 (double)

What I now want to do is to add a constraint so that there is always one column (either column1 or column2) that has a null value. I have tried the following:

ALTER TABLE mytable 
    ADD CHECK (
        (column1 IS NOT NULL && column2 IS NULL) || 
        (column2 IS NOT NULL && column1 IS NULL)
    )

But it doesnt seem to be working since I still can have cases like this:

CASE1:
------
name: bla
column1: null
column2: null

CASE2:
------
name: bla
column1: somevalue
column2: 123

How can I get this working so that I get an error when I try case1 and case2?

(Additionally: if my memory serves me well: the constraint I used can be shortened, but I can't remember how it was done. So I would be happy if someone helped me with that as well!)

Answer 1

Ok, I guess this is what you want to do:

delimiter $$
DROP TRIGGER IF EXISTS trg_check_bi$$

CREATE TRIGGER trg_check_bi BEFORE INSERT ON mytable
FOR EACH ROW 
BEGIN 
    IF(
        (NEW.column1 IS NULL AND NEW.column2 IS NULL) || 
        (NEW.column2 IS NOT NULL AND NEW.column1 IS NOT NULL)
    )
    THEN
        SIGNAL SQLSTATE '44000'
            SET MESSAGE_TEXT = 'check constraint failed';
    END IF;
END$$
delimiter ;

Basically, this trigger checks values before insert, and throws user defined error. You should do the same with BEFORE UPDATE trigger. I hope this helps.

Here's the SQLFiddle, just add value for column2 in insert statement (can't save fiddle that fails :))