SQL Server 索引管理——唯一索引和唯一约束的区别

 

SQL Server 索引管理——唯一索引和唯一约束的区别

 

唯一索引保证在索引键列中的值是唯一的

唯一约束保证没有重复值被插入到列中，当该列上创建有唯一约束的时候。当列上创建了唯一约束时，对应的会在该列自动创建唯一索引

为验证这些差异，我们创建一个测试表，创建完成后，使用sp_helpindex查看表的索引情况

CREATE TABLE uniqueTest(id INT NULL);
EXECUTE sp_helpindex uniqueTest;

此时表上并没有索引，然后我们在表的id列增加唯一约束

ALTER TABLE uniqueTest ADD CONSTRAINT uq_test UNIQUE(id);

再使用sp_helpindex查看表上的索引情况，结果如下：

此时在uniqueTest表的id列创建了一个非聚集唯一索引。向表id列中两次插入1

INSERT INTO uniqueTest VALUES(1);
GO 2

可以看到第一次插入成功，第二次在id 列中插入1时，因为和之前插入的1值重复，报2627，违反唯一约束错误。同时，当在唯一列中插入两个NULL值时，也会报错。

要使得重复键不插入，也不报错，可以设置 IGNORE_DUP_KEY=ON，开启忽略重复选项

--删除唯一约束
ALTER TABLE uniqueTest DROP CONSTRAINT uq_test;
--重建唯一约束，开启忽略重复键
ALTER TABLE uniqueTest ADD CONSTRAINT uq_test UNIQUE(id)
WITH (IGNORE_DUP_KEY=ON);

唯一键约束自动创建的索引不能显示删除，如下我们删除索引uq_test

DROP INDEX [uq_test] ON uniqueTest;

删除唯一键约束，其自动创建的索引会随之删除

NOCHEC CONSTRAINT 对 UNIQUE CONSTRAINT 不起作用

--禁用所有约束
ALTER TABLE uniqueTest NOCHECK CONSTRAINT ALL;
DELETE FROM uniqueTest WHERE id IS NULL;
INSERT INTO uniqueTest VALUES(NULL);
GO 2

虽然禁用了表上的所有约束，但重复键NULL仍然不能插入列中。

然而，当我启用约束，禁用唯一约束创建的索引时，发现可以插入重复值了。

--起用所有约束
ALTER TABLE uniqueTest CHECK CONSTRAINT ALL;
--禁用唯一约束创建的索引
ALTER INDEX uq_test ON uniqueTest DISABLE;
DELETE FROM uniqueTest WHERE id IS NULL;
INSERT INTO uniqueTest VALUES(NULL);
GO 2

发现一件有意思的事是，在表的同一个列上可以重复创建唯一键

ALTER TABLE uniqueTest ADD CONSTRAINT uq_test1 UNIQUE(id)

然后使用sp_helpconstraint 查看表上的约束

--查看表上的约束
exec sp_helpconstraint uniqueTest

或者直接通过ssms查看如下

两个唯一约束都存在，删除重复的约束

ALTER TABLE uniqueTest DROP CONSTRAINT uq_test1;

可以通过如下脚本查看唯一约束的信息：

--查看unique 约束信息的相关脚本
--查看表上所有约束，包括default、check、unique、primary key、foreign key 状态等信息
EXECUTE sp_helpconstraint uniqueTest;
--查看unique 约束的创建时间、修改时间等
SELECT * FROM sys.key_constraints
WHERE OBJECT_ID=OBJECT_ID('uq_test',N'UQ');
SELECT * FROM sys.objects
WHERE OBJECT_ID=OBJECT_ID('uq_test',N'UQ');
SELECT * FROM sysconstraints
WHERE constid=OBJECT_ID('uq_test',N'UQ');

下面，我们删除唯一约束，并在表上创建非聚集唯一索引，再向表中插入重复的NULL值

--删除约束
ALTER TABLE uniqueTest DROP CONSTRAINT uq_test;
ALTER TABLE uniqueTest DROP CONSTRAINT uq_test1;
--清空表中已有的NULL值
DELETE FROM uniqueTest WHERE id IS NULL;
--创建非聚集唯一索引
CREATE UNIQUE INDEX uix_test ON uniqueTest(id);
INSERT INTO uniqueTest VALUES(NULL);
GO 2

可以看到，和唯一值约束一样，第一次插入NULL成功，第二次插入NULL时失败，报不能在唯一索引中插入重复键行错误。

现在，可能有这样的一个情景，就是非NULL值不允许重复，但NULL可以重复，我们可以修改重建索引，对索引键值进行筛选，索引不包含NULL值，这样我们就可以实现上面的要求了

CREATE UNIQUE INDEX uix_test ON uniqueTest(id)
WHERE id IS NOT NULL
WITH(DROP_EXISTING=ON);
INSERT INTO uniqueTest VALUES(2);
GO 2

插入非NULL重复值时，第一次成功，第二次报重复错误，然后我们再重复插入NULL值

两次均成功。

结论：

对比唯一约束和唯一索引，我们发现，两者在完成唯一性的功能上是完全一致的，同时两者在性能上也是没有差别的；唯一约束的优点在于其可以使用筛选索引限定哪些值可以重复，哪些值必须唯一，这一点是唯一约束没有办法实现的，所以，建议在实现唯一性上，使用唯一索引。

如果喜欢，可以搜索关注 MSSQLServer 公众号，将有更多精彩内容分享：