具有空值的 SQL Server 2005 唯一键 [英] SQL Server 2005 unique key with null value

问题描述

我在 SQL Server 2005 中有一个带有外键的表，我希望该外键是唯一值或空值.我已将其设置为唯一键，但它不允许我在同一个表中有多个空值.可以做我想做的吗?

I have a table in SQL Server 2005 with a foreign key, and I want that foreign key to be a unique value or null. I've set it up as a unique key, but it won't allow me to have multiple nulls in the same table. Is it possible to do what I want?

推荐答案

这是对 SQL Server 的唯一约束/索引的长期抱怨.最好的解决方案是创建一个带有模式绑定的视图，然后在该列上放置一个唯一索引:

This is a long time complaint about SQL Server's Unique constraints/indexes. The best solution is to create a view with schemabinding and then put a unique index on that column:

Create View dbo.MyUniqueColView
With SchemaBinding
As
Select MyColToBeUnique
From MyTable
Where MyColToBeUnique Is Not Null

GO

Create Unique Clustered Index IX_MyTable_MyColToBeUnique On MyUniqueColView ( MyColToBeUnique )

这篇关于具有空值的 SQL Server 2005 唯一键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！