在 SQL Server 中实现允许多个 NULL 值的唯一约束的正确方法 [英] The proper way to implement unique constraint that allows multiple NULL values in SQL Server
问题描述
我需要表中的 1 列来保存唯一的非空值或 NULL
.TSQL UNIQUE
约束将 2 个 NULL
视为相等,因此我无法使列唯一.
处理这个问题的正确方法是什么?
经过一番研究,我发现了两种对我来说似乎正确的方法,但我无法确定哪一种更好.
第一个不适用于所有情况:
I need 1 column in the table to hold unique non-null values or NULL
. TSQL UNIQUE
constraint treats 2 NULL
s as equal, so I cannot make column unique.
What is the right way to handle this problem?
After doing some research, I found 2 methods which seem correct to me, but I cannot determine which one is better.
The first which is not applied to all cases:
CREATE TABLE test (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
null_or_unique_id INT, unique_key AS
(CASE WHEN [null_or_unique_id] IS NULL THEN -(1)*[id]
ELSE [null_or_unique_id] END), UNIQUE(unique_key ));
它可以工作,但要求 null_or_unique_id
的所有允许值都为非负(在我的情况下没问题)
第二个:
It works but requires all allowed values of null_or_unique_id
to be non-negative (that's ok in my case)
The second one :
CREATE VIEW test_view WITH SCHEMABINDING AS
SELECT [null_or_unique_id] FROM dbo.test WHERE [null_or_unique_id] IS NOT NULL;
GO
CREATE UNIQUE CLUSTERED INDEX byNullOrUniqueId
ON dbo.test_view([null_or_unique_id]);
当然,也可以使用触发器来实现所需的功能,但我认为触发器解决方案会产生比上述任何一种都更多的开销.
Surely, it's also possible to implement the desired functionality with triggers, but I think trigger solution will create more overhead then any of mentioned above.
这种情况的最佳实践是什么?
感谢您的回答.
What is the best practice for such a case?
Thanks for your answers.
推荐答案
4 种方式:
- 过滤索引 (SQL Server 2008) <- 基于推荐在您的标签上
- 触发器(提及)
- 索引视图(在您的问题中)
- 具有计算列的唯一约束/索引(在您的问题中)
- Filtered index (SQL Server 2008) <- recommended based on your tags
- Trigger (mentioned)
- Indexed view (in your question)
- Unique constraint/index with computed column (in your question)
这篇关于在 SQL Server 中实现允许多个 NULL 值的唯一约束的正确方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!