mysql程序中的非空,MySQL中的非空约束

What's the best way to create a non-NULL constraint in MySQL such that fieldA and fieldB can't both be NULL. I don't care if either one is NULL by itself, just as long as the other field has a non-NULL value. And if they both have non-NULL values, then it's even better.

解决方案

MySQL 5.5 introduced SIGNAL, so we don't need the extra column in Bill Karwin's answer any more. Bill pointed out you also need a trigger for update so I've included that too.

CREATE TABLE foo (

FieldA INT,

FieldB INT

);

DELIMITER //

CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo

FOR EACH ROW BEGIN

IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';

END IF;

END//

CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo

FOR EACH ROW BEGIN

IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';

END IF;

END//

DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK

INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error

UPDATE foo SET FieldA = NULL; -- gives error