MySQL中的OR约束或非null约束 [英] Either OR non-null constraints in MySQL

问题描述

在MySQL中创建非NULL约束以使fieldA和fieldB不能都为NULL的最佳方法是什么.我不在乎任何一个本身是否为NULL，只要另一个字段具有非NULL值即可.而且，如果它们都具有非NULL值，那就更好了.

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引入了

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

这篇关于MySQL中的OR约束或非null约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！