CODEWITHSUNDEEP
mysqlsqldatabase
Seb
Seb

Reputation: 117

Not null if other entry is not null

I am writing a table and I need A to not be null if B is not null (see below). I am trying to write a CHECK however I am not sure how to write it. Any advise is much appreciated.

CREATE TABLE `Test`
`A` varchar(4) CHECK(?),
`B` varchar(4)
);

Upvotes: 0

Views: 47

Answers (2)

Ted Hopp
Ted Hopp

Reputation: 234795

Don't bother with a CHECK constraint. According to the manual:

The CHECK clause is parsed but ignored by all storage engines.

You can instead use a trigger, as suggested in this thread. The test itself would be

(B is null) or (A is not null)

EDIT: Assuming you are using MySQL version 5.5 or later, here's how you can trigger an error signal when the condition is violated:

mysql> DELIMITER //
mysql> DROP TRIGGER IF EXISTS before_insert_trigger//
mysql> CREATE TRIGGER before_insert_trigger
    ->   BEFORE INSERT ON Customer FOR EACH ROW 
    ->   BEGIN
    ->   DECLARE msg VARCHAR(255);
    ->       IF (NEW.B is not null) and (NEW.A is null) THEN 
    ->           SET msg = concat('Error: Trying to insert a NULL A when B is not null: ',
    ->                            cast(NEW.B as char));
    ->           SIGNAL sqlstate '45000' SET message_text = msg;
    ->       END IF; 
    ->   END//
mysql> delimiter ;

(Note that I inverted the test because I want to test when it is violated, not when it is satisfied.) If you are using an earlier version of MySQL, you can use the suggestion provided in this answer or here.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269693

MySQL does not enforce check constraints. So, the only way you can do this in MySQL (using one table) is to use triggers.

I think the check constraint syntax you want is:

check ((a is null and b is null) or (a is not null and b is not null))

although it might be:

check ((b is null) or (a is not null))

depending on whether you really mean "if" or "if and only if".

Upvotes: 1

Related Questions