SQL Server - 唯一索引 vs 唯一约束 - 关于重复值

Question

唯一索引确保索引键列中的值是唯一的。 唯一约束保证在创建约束所在的列上不能插入重复值。当创建唯一约束时，相应的唯一索引会自动创建在列上。

问题:

1. 如果我们在列上有唯一索引但没有唯一约束，是否可以插入重复值？
2. 对于任何列中的现有重复项，它是否允许创建唯一索引或唯一约束？

- variable

Answer 1

如果在列上存在唯一索引且没有唯一约束，则通常无法插入重复值，会引发错误。以下是例外情况：

1. 使用 IGNORE_DUP_KEY 选项创建索引。不会引发错误且插入操作被忽略。

2. 非聚集索引被过滤，使得重复值不满足索引的 WHERE 子句。行将被插入但不会反映在非聚集索引中。

对于任何列上的现有重复项，除了上述提到的过滤索引之外，都不允许创建唯一索引或唯一约束。

- Dan Guzman

---

需要澄清的是，使用 IGNORE_DUP_KEY 并不允许索引中存在重复的值。唯一索引从不含有重复的值。而 IGNORE_DUP_KEY 仅仅是使尝试插入重复值的操作被忽略（并附有一个警告信息），而非导致错误。 - John Rees

Answer 2

1. 如果我们在某列上有唯一索引但没有唯一约束，是否可以插入重复值？

不行，索引内的列值必须创建一个唯一的数据集。

2. 如果该列中存在重复项，能否创建唯一索引或唯一约束？

不行，您不能在具有重复值的表上创建唯一索引。

最简单的方法是尝试操作（我建议对于这种事情这样做，这是学习的好方法）：

CREATE TABLE dbo.SomeTable (SomeInt int, AnotherInt int);
GO

INSERT INTO dbo.SomeTable (SomeInt,
                           AnotherInt)
VALUES (1,1),
       (1,2),
       (2,1);
GO
--Create a unique index on a column with duplicate values
CREATE UNIQUE INDEX UQ_SomeInt ON dbo.SomeTable(SomeInt); --fails
GO
--Create a unique index on the 2 columns, as they are unique
CREATE UNIQUE INDEX UQ_Some_AnotherInt ON dbo.SomeTable(SomeInt, AnotherInt); --Succeeds
GO
--Try to insert a duplicate value
INSERT INTO dbo.SomeTable (SomeInt,
                           AnotherInt)
VALUES(2,1); --fails
GO

SELECT *
FROM dbo.SomeTable
GO
DROP TABLE dbo.SomeTable;

- Thom A

Answer 3

一个可能不直观的场景，一开始让我感到困惑：Postgres 不会将 NULL 值视为相等。 如果你的表看起来像这样：

+-------+-------+-------+
|id     |a      |b      |
+-------+-------+-------+
|1      |0      |NULL   |
|2      |0      |NULL   |
+-------+-------+-------+

您仍然可以在列a和b上添加唯一索引。根据Postgres，具有ID 1和ID 2的行在列a上具有相同的值，但在列b上具有不同的值。

- Pearce Merritt

---

同样适用于 MySQL / MariaDB - lrkwz