唯一约束，仅当字段包含特定值时生效。

Question

我正在尝试为两列创建一个唯一的索引约束，但仅当另一列包含值1时才生效。例如，只有当“active = 1”时，“column_1”和“column_2”才应该是唯一的。任何包含“active = 0”的行都可以与具有另一行的“active”值无关的行共享“column_1”和“column_2”的值。但是具有“active = 1”的行不能与具有“active = 1”的另一行共享“column_1”或“column_2”的值。

所谓“共享”，是指两行具有相同的列值。例如：row1.a = row2.a AND row1.b = row2.b。仅当row1中的两个列与row2中的其他两个列匹配时，才会共享值。

希望我表述清楚。：\

- Sam

Answer 1

你可以尝试使用column_1，column_2和active创建多列唯一索引，并在不需要唯一性的行上设置active=NULL。或者，你可以使用触发器(参见MySQL触发器语法)，并检查每个插入/更新的行是否已经存在于表中 - 但我认为这可能会比较慢。

- Paul Lysak

---

我建议使用NULL解决方案 - 这很容易，NULL等于FALSE（只是不要对一个原教旨的SQLer这么说），最重要的是它可以让你的数据库为你完成所有工作。 - Xavier Holt

在NULL解决方案中，当两行数据完全相同时，数据库如何区分它们的唯一性？MySQL是否会避免此时的约束检查？ - decached

@A.K. 在 MySQL 中，NULL 不是一个值，因此它并不具有偏见。而 Active='YES'（与 Active=NULL 相对）是一个值，可以确立唯一性。 - veritas

Answer 2

我想为两列创建一个唯一索引约束，但只有当另一列包含值1时才生效。您可以将“另一列”的值设置为不等于1的唯一值，例如记录的id。然后可以将唯一索引约束应用于包括“另一列”在内的所有三列。我们称“另一列”为columnX。如果要应用唯一约束，请将columnX的值设置为1。如果不想应用唯一约束，请将columnX的值设置为唯一值。这样就不需要额外的工作/触发器了。唯一索引约束能解决您的问题。

- Lewis Z

---

当设置 NULL 不是一个选项时，这是一个绝妙的主意！ - Paul van Schayck

Answer 3

我不确定 MySQL 语法，但它应该与 SQL Server 拥有几乎相同的内容：

CREATE UNIQUE INDEX [UNQ_Column1Column2OnActive_MyTable]
  ON dbo.[MyTable]([column1,column2)
  WHERE   ([active] = 1);

这个索引将确保如果 active=1，则在整个表中 column1 和 column2 的组合是唯一的。

- Ibo

---

谢谢您提供的示例，这对我们来说是个重大的改变。为了帮助其他人研究，这些索引被称为筛选索引。以下是来自Microsoft官方链接：https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes - Airn5475

Answer 4

在SQL Server中，可以通过检查约束来实现此目的，但我不知道MySQL是否支持类似的功能。

在任何数据库上都可以使用的方法是将表分成两个部分。如果active = 0的记录只是历史记录，并且永远不会再次变为活动状态，则可以将它们移动到另一个表中，并在原始表上设置一个简单的唯一约束。

- SWeko

---

我在IRC上得到了一个类似的建议：创建一个活动表和一个非活动表。但是你的方式更有意义；有一个历史表和一个活动表。然而，这个表有很多字段，将这些字段复制到两个表中似乎是错误的做法。你会如何建议我避免这种冗余？ - Sam

稍微反规范化一下就可以大有作为，所以不要担心重复定义。当然，您还需要将对原始表的任何更改应用到历史表中，这会增加一些开销，但是，在我看来，这样做非常值得，可以保持数据的清洁。 - SWeko

嗯，如果我只分离需要独立索引的数据会怎样呢？表格：mydata，mydata_active，mydata_inactive。这样，mydata包含主键和数据列。而mydata_active和mydata_inactive将仅包含需要唯一索引的列和一个外键，该外键引用mydata。这是另一种方式，但需要额外（第三个）表格。 - Sam

这也可以实现，但是可能会有太多的开销，因为任何涉及到这些表的查询都必须额外连接。如果历史数据很少使用，那么你应该基本上将它们藏在角落里，然后忘记它们。 - SWeko

比较晚加入，但补充一下 @SWeko 的回答: 如果您使用的是 Microsoft SQL Server，也请参考这里建议的过滤索引: https://dev59.com/s3NA5IYBdhLWcg3wrf83#5149263 - Jirajha

Answer 5

我不确定我完全理解你的意思，但是假设你有一个带有状态列的表格，并且你想确保只有一行状态为“ A”（活动）。 你可以接受许多具有“ I”或“ Z”或其他任何状态的行。 只允许一行状态为“ A”。

这将解决问题。

  CREATE UNIQUE INDEX [Idx_EvalHeaderOnlyOneActive]
  ON [dbo].[EvalHeader]([Hdr Status])
  WHERE [Hdr Status] = 'A';

- Jorge Serrano

Answer 6

索引是不受外部影响的。这种约束必须在数据库之外实现。

- Ian Wood