MySQL中的OR非空约束
什么是在MySQL中创建非NULL约束的最佳方式,以至于fieldA和fieldB不能都是NULL。我不关心这两个字段中的任何一个是否为NULL,就像其他字段具有非NULL值一样。如果它们都具有非NULL值,那就更好了。MySQL中的OR非空约束
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
我已经做了在SQL Server类似的东西,我不知道这是否会在MySQL直接工作,但:
ALTER TABLE tableName ADD CONSTRAINT constraintName CHECK ((fieldA IS NOT NULL) OR (fieldB IS NOT NULL));
至少我认为是这样的语法。
但是,请记住,您无法在表间创建检查约束,只能检查一个表内的列。
这是这样一个约束标准语法,但MySQL的幸福忽略了约束后
ALTER TABLE `generic`
ADD CONSTRAINT myConstraint
CHECK (
`FieldA` IS NOT NULL OR
`FieldB` IS NOT NULL
)
@Sklivvz:与MySQL 5.0.51a测试,我发现它解析CHECK约束,但不强制它。我可以插入(NULL,NULL),没有错误。测试了MyISAM和InnoDB。随后使用SHOW CREATE TABLE显示CHECK约束不在表定义中,即使在定义表时没有给出错误。
这匹配MySQL manual它说:“CHECK子句被解析,但被所有存储引擎忽略。”
因此,对于MySQL,您将不得不使用触发器来执行此规则。唯一的问题是MySQL触发器无法引发错误或中止INSERT操作。在触发器中可以做的一件事情是将NOT NULL列设置为NULL。
CREATE TABLE foo (
FieldA INT,
FieldB INT,
FieldA_or_FieldB TINYINT NOT NULL;
);
DELIMITER //
CREATE TRIGGER FieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
SET NEW.FieldA_or_FieldB = NULL;
ELSE
SET NEW.FieldA_or_FieldB = 1;
END IF;
END//
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
您还需要一个类似的触发器BEFORE UPDATE。
这不是直接回答你的问题,而是一些额外的信息。
当多列处理,并检查是否全部为空或一个不为空,我通常使用COALESCE()
- 这是短暂的,可读性和易于维护的,如果名单不断增加:
COALESCE(a, b, c, d) IS NULL -- True if all are NULL
COALESCE(a, b, c, d) IS NOT NULL -- True if any one is not null
这可以用在你的触发器。
由于这是标准的SQL语法,所以也应该与其他基于SQL的数据库一起工作。 (它绝对适用于PostgreSQL。) – Neall 2008-10-04 17:06:27
MySQL手册说:“CHECK子句已解析但被所有存储引擎忽略。”我试过了,这是真的。 – 2008-10-04 17:23:45