MySQL 问题 - 唯一键无法正常工作，或者我误解了？

Question

我正在尝试创建一种关系，其中可以包含四个不同部分中的任何一个，但相同部分的任何集合都应视为唯一的。

例子： 任务必须有指定的公司，可以选择指定地点、工作组和计划。 分配可能没有没有位置的工作组。

假设我们有公司 A、B、C； 位置 X、Y、Z； 工作组 I、J、K 和计划 1、2、3。

因此有效关系可以包括 A-X-I-1 A-Z-2 经过 C C-3 B - Z - K

但无效关系包括 A - K（无位置的工作组） Y - K - 1（没有公司）

所以，为了创建我的表，我创建了

companyID INT NOT NULL,
FOREIGN KEY companyKEY (companyID) REFERENCES company (companyID),
locationID INT,
FOREIGN KEY locationKEY (locationID) REFERENCES location (locationID),
workgroupID INT,
FOREIGN KEY workgroupKEY (workgroupID) REFERENCES workgroup (workgroupID),
programID INT,
FOREIGN KEY programKEY (programID) REFERENCES program (programID),
UNIQUE KEY companyLocationWorkgroupProgramKEY (companyID, locationID, workgroupID, programID)

我认为这可以处理我所有的关系，除了需要有一个工作组（我可以很高兴地以编程方式完成）的任务之外，还有一个位置或者使用触发器，我认为）

但是，当我测试这个模式时，它允许我输入以下内容......

INSERT INTO test VALUES (1, null, null, null), (1, null, null, null);

...没有抱怨。 我猜测 (1, null, null, null) 不等于自身，因为包含了 null。 如果是这样的话，我有什么办法可以处理这种关系吗？

任何帮助，将不胜感激！

Answer 1

这是一个功能（尽管也不是我所期望的）。

此线程建议将您的密钥设为主键以获得您期望的行为：

这是一个功能 - NULL 值是
未定义值，因此两个 NULL
值不一样。 可以是一个
有点令人困惑，但当
你考虑一下。

UNIQUE 索引确实可以确保
非 NULL 值是唯一的； 你可以
指定您的列不接受
NULL 值。

Answer 2

我能想到的在没有额外触发器/编程的情况下处理此问题的唯一方法是在每个引用的表中都有一个“以上都不是”值，以便您的测试看起来像

INSERT INTO test VALUES (1, NO_LOCATION, NO_WORKGROUP, NO_PROGRAM),
                        (1, NO_LOCATION, NO_WORKGROUP, NO_PROGRAM)

NO_* 标识符是 ID 列的正确类型/长度。 正如您所期望的那样，这将失败。

Answer 3

在 MySQL 中 NULL != NULL 或其他任何东西。 所以这就是 UNIQUE 不起作用的地方。 您应该为空白使用另一个默认值，例如零

Answer 4

我认为重要的是要注意，有一种正确的方法可以解释和处理 NULL 值，并且 OP 所表现出的行为正是预期的。 您可以忽略这种行为，并且可以以任何您想要的方式处理您的查询，而不会受到我的反对，但最好“接受”描述某种形式的最佳实践的答案，而不是非标准的个人偏好。

或者，如果您不同意共识最佳实践，您可以不接受任何答案。

这不是一场尽快让答案被接受的竞赛。 我认为，审议和协作也应该成为该过程的一部分。

Answer 5

我看到这个问题是在 2009 年提出的。但是 MySQL 经常会提出这样的要求： https:// /bugs.mysql.com/bug.php?id=8173 和 https例如：://bugs.mysql.com/bug.php?id=17825。 人们可以点击“影响我”来尝试引起 MySQL 的关注。

从 MySQL 5.7 开始，我们现在可以使用以下解决方法：

ALTER TABLE test 
ADD generatedLocationID INT AS (ifNull(locationID, 0)) NOT NULL,
ADD generatedWorkgroupID INT AS (ifNull(workgroupID, 0)) NOT NULL,
ADD generatedProgramID INT AS (ifNull(programID, 0)) NOT NULL,
ADD UNIQUE INDEX (companyID, generatedLocationID, generatedWorkgroupID, generatedProgramID);

生成的列是虚拟生成的列，因此它们没有存储空间。 当用户插入（或更新）时，唯一索引会导致动态生成生成列的值，这是一个非常快速的操作。