5

(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!)

Zagor23
  • 1,953
  • 12
  • 14
J. Rahmati
  • 735
  • 10
  • 37
  • MySQL does not support check constraints at all. You can define them but they are ignored. Use a trigger instead. – juergen d Jan 16 '14 at 10:44
  • Possible duplicate - http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working – Zagor23 Jan 16 '14 at 11:03
  • @Zargor23, the link assumes that I can set the value, but I don't want to set a value, I want to prevent both columns to have a value. Its either column1 has a value or column2. If you try to do otherwise an error should occur! – J. Rahmati Jan 16 '14 at 11:57
  • It looks like MySQL 8+ has `CHECK` constraints: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html – Chris Calo Jul 05 '21 at 18:39

1 Answers1

4

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 :))

Zagor23
  • 1,953
  • 12
  • 14