基于列值的MySQL唯一约束

Question

假设我有这样的表格：

CREATE TABLE dept (
  id     VARCHAR(255) NOT NULL PRIMARY KEY,
  code   VARCHAR(255) NOT NULL,
  active BIT          NOT NULL,
  ...
);

问题:

我想在code列上添加唯一约束。但是，它只应在active设置为true时应用(唯一性应仅在活动记录之间检查)。可以有许多具有active=false和相同code的记录，因此我不能在多个列上使用约束。

我尝试过的:

我在文档中没有找到任何参考证明这种约束是可能的，但我知道在其他数据库中可以使用唯一函数索引实现。

当然，我可以编写触发器，在每个添加/更新操作上检查不变量，但我希望有更有效的解决方案。

我正在使用MySQL 5.7.15。

- Sasha Shpota

---

很抱歉，在MySQL中这是不可能的。你最接近的方法可能是创建一个唯一约束的可空列（替换活动字段）。当为NULL时，它是“非活动”的；当为除NULL以外的任何值时，它必须是唯一的。否则，通过存储过程读写表格或使用触发器进行一些不太优雅的操作。 - wally

可能有更优雅的解决方案，但这需要我们了解更大的背景（也就是说 - 您试图解决的整体问题）。您能否更新您的问题并提供完整的情况描述？ - wally

Answer 1

很抱歉，在MySQL中无法实现该功能。

过去，我曾通过使用一个唯一约束的可空列（替换“活动”和“代码”字段）来“接近”解决此问题。 当为“NULL”时，它是“非活动的”，当为除“NULL”以外的任何值时，它必须是唯一的。

但这并没有完全解决您所提出的问题。 （如果您可以更新您的问题以包含更全面的情况，可能会有更好的建议。）

否则，通过存储过程读写表格或像你自己建议的那样使用触发器进行不优雅的操作。

- wally

---

我不能将“代码”设置为“NULL”，以便对于非活动记录仍然是有价值的业务信息（例如，该记录稍后可能会被移回活动状态）。 - Sasha Shpota

啊，好的 - 没问题。如果没有看到更大的图片，我认为你可能会被困在一个事务安全的过程中，用于读/写表格并更新具有唯一约束的单独列。不幸的是，尽管MySQL很好，但它没有其他一些DBMS所具有的许多功能... :-) - wally

这确实很遗憾。也许MySQL并不是我们做出的最佳选择，但不幸的是现在改变已经太晚了。这已经是我列表中第四个重要功能，在MySQL中缺失而在其他免费数据库中存在。另一方面，谁知道，也许MySQL中还有其他重要的功能，而这些功能在那些数据库中却缺失。 - Sasha Shpota

我喜欢把MySQL看作是“拥有DBMS所需的一切...但缺少许多人想要的东西”的平台。它是一个出色的平台，具有事务安全性、惊人的性能、复制、权限等功能。但对于许多情况，你必须更加努力才能得到你想要的东西。 - wally

Answer 2

为了解决您的问题，您需要使用CHECK子句，但是MySQL不支持它。从文档中可以看到：

CHECK子句被所有存储引擎解析但被忽略。请参见第13.1.18节“CREATE TABLE Syntax”。接受但忽略语法子句的原因是为了兼容性，使从其他SQL服务器移植代码更容易，并运行创建带有引用表的应用程序。

所以，您只能通过在应用程序级别上检查数据或通过存储过程插入/更新此表中的行来完成此操作。

- Artem Ilchenko

Answer 3

很抱歉这并不是直接回答您的问题：

也许您最好采用不同的表设计？您想要做的事情不受关系型数据库管理系统支持，这实际上是您使用它的错误证据。

您是否考虑过创建一个dept和dept_history表，dept仅包含活动记录？这将解决您在唯一约束方面遇到的问题。

- Daniel

---

这是一种额外的负担。MySQL不支持CHECK子句并不意味着数据库设计有误 - 几乎所有其他数据库都支持此功能。 - Sasha Shpota

这里只是提供另一种替代方案。我无法评判您的DB-Design，因为我不了解它，但是您肯定想要使用MySQL（关系型数据库管理系统）超出其功能范围。您认为支持检查子句的RDBMS会引入什么样的开销呢？ - Daniel

当然这是一个权衡。肯定有原因。但是没有这样的功能，责任就转移到了开发人员身上，他们肯定会以不那么高效的方式实现它。我宁愿拥有这个功能及其所有的附加开销/后果，而不是自己去实现它。 - Sasha Shpota