Need help in creating a unique constraint in SQL Server table

pkwftd7m 于 2023-06-21 发布在 SQL Server

关注(0)|答案(1)|浏览(141)

We have a WorkOrder table in Integration. I want to ensure the combination of WorkId,Key1,Key2,Key3 is unique. But values in Key1 , Key2,Key3 columns are nullable and can be either null or ‘’.

I tried implementing this by the below index. But for some reason it is still allowing me to have 2 rows with the same combination of WorkId,Key1,Key2,Key3

Create Unique NonClustered Index WorkOrder_NCI_Uniqueness1
on WorkOrder (WorkId,Key1,Key2,Key3)
where [Key1] is null and [Key2] is null and [Key3] is null and WorkId is not null

sql-server

来源：https://stackoverflow.com/questions/76413744/need-help-in-creating-a-unique-constraint-in-sql-server-table

1条答案

按热度按时间

9njqaruj1#

You can handle NULL and '' as equivalent by means of a view using ISNULL(, '') or NULLIF(, '') for the Key columns.

CREATE VIEW dbo.MYVIEW WITH SCHEMABINDING AS
SELECT WorkId,ISNULL(Key1,'')Key1,NULLIF(Key2,'')Key2,NULLIF(Key3,'')Key3 FROM dbo.MYTABLE

CREATE UNIQUE CLUSTERED INDEX MYVIEWINDEX ON dbo.MYVIEW(WorkId,Key1,Key2,Key3)

If there is a unique index on the table and you want an additional control for NULL and '' you can append “WHERE (Key1=''OR Key1 IS NULL)OR(Key2=''OR Key2 IS NULL)OR(Key3=''OR Key3 IS NULL)” to focus the indexed view on those values, comparable with a filtered index, and minimize the data duplication.

赞(0）回复(0）举报 2023-06-21

我来回答

Need help in creating a unique constraint in SQL Server table

1条答案

相关问题

热门标签

最新问答