在MySQL中创建非NULL约束的最佳方法是什么,fieldA和fieldB不能都是NULL。我不关心其中任何一个本身是NULL,只要其他字段具有非NULL值。如果它们都具有非NULL值,那么它甚至更好。
发布于 2018-08-27 12:59:05
MySQL 5.5引入了SIGNAL,因此我们不再需要Bill Karwin的答案中的额外列。比尔指出你还需要一个更新触发器,所以我也包括了。
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
https://stackoverflow.com/questions/-100000660
复制相似问题