MySQL：约束一组列，以便至少有一个不为NULL

Question

我希望有一个包含两个列的SQL表。一个是另一张表的关键字，另一个包含字符串文本。这意味着电话号码可以精确输入（在这种情况下使用电话簿表中的ID），也可以使用通配符（在这种情况下使用字符串文本）。因此，表中的一列将保存一个值，另一个将保留NULL。

是否可能约束该表以使一列必须有值，而另一列必须为NULL？如果两个列都为NULL或都有值，则行无效。我有一种感觉MySQL不能做到这一点（因为它在约束方面似乎没有全面的工具箱），但询问一下也无妨。

- GordonM

---

这可能会有所帮助（虽然不完全相同）：https://dev59.com/3VLTa4cB1Zd3GeqPeeRD - WhyNotHugo

Answer 1

我不知道如何强制执行这样的约束条件。

作为一种解决方法，您可以考虑拥有两个不同的列：如果您有一个用于数据的列 - 包含电话簿ID或字符串文字，以及另一个用于数据类型的列 - 无论是“确切”还是“通配符”，您都可以将NOT NULL约束条件设置为这两个列。一个明显的缺点是您不能再对phonebooks表设置FK约束了。

- titanoboa

---

我会接受这个，但我实际采用的设计有些不同。我添加了一个可以是精确匹配或通配符的Kind字段，Key字段的内容将根据kind字段的值指向电话簿或新的通配符表。Kind和Key是组合唯一键，因此Key可以包含相同的值两次（一次用于电话簿的键，一次用于通配符的键）。 - GordonM

Answer 2

自从 GENERATED 列问世以来，这就成为了可能。

CREATE TABLE `test_multiple_not_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `idOne` int(11) DEFAULT NULL,
  `idTwo` int(11) DEFAULT NULL,
  `not_null_constrain` int(11) GENERATED ALWAYS AS (coalesce(`idOne`,`idTwo`)) VIRTUAL NOT NULL,
  PRIMARY KEY (`id`)
);

由于每次插入行时，生成的列必须运行以检查它是否满足NOT NULL约束条件，如果违反该限制，它将返回错误代码1048: Column 'not_null_constrain' cannot be null。

- Akxe

Answer 3

您可以创建触发器在插入前运行，检查值并确定是否应该进行插入或更新。有关如何创建此类触发器的良好示例，请参见此处：https://dba.stackexchange.com/questions/43284/two-nullable-columns-one-required-to-have-value

- Drogos

Answer 4

以下触发器对我有效：

CREATE TRIGGER tgr_OrgFeeOwnerInsert
    BEFORE INSERT
    ON OrganisationFee
    FOR EACH ROW
BEGIN
    IF (SELECT ((new.fieldA IS NULL) + (new.fieldB IS NULL) + (new.fieldC IS NULL)) <> 2)
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'my error message';
    END IF;
END;

CREATE TRIGGER tgr_OrgFeeOwnerUpdate
    BEFORE UPDATE
    ON OrganisationFee
    FOR EACH ROW
BEGIN
    IF (SELECT ((new.fieldA IS NULL) + (new.fieldB IS NULL) + (new.fieldC IS NULL)) <> 2)
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'my error message';
    END IF;
END;

- Leukipp

---

第二个触发器应该是BEFORE UPDATE吗？对我来说，除了分配的名称之外，这两个触发器看起来都是一样的。 - shmulvad