What is SQL Server’s unique index, per subset of data, based on values of another column?
Say I have a Customers table, and an Emails table. The emails table has these fields: Id CustomerId EmailValue Now, I want to create a constraint so that no duplicate emails should be registered per customer. Ways that I can think of are:
Creating a function to ensure the uniqueness of each new email value, and call that function in a check constraint Create a trigger, and on each insert or update ensure that the email is not duplicate per customer Forget about the database at all, and make this part of the business in my application tier
However, I recently got familiar with SQL Server's filtered indexes. Though I can't find out if it can help me in this specific case or not. Most of the examples I've seen is to filter out NULL values in order to make a unique index for a nullable column. Is there a way I can use a filtered index to achieve what I want? Create a unique index SQL server constraint over two columns. alter table dbo. Emails add constraint UQ_Emails unique (CustomerId, EmailValue); This will prevent duplicate emails per customer. Different customers can still have the same email.
From comments: What if I have an IsDefault field too? In that case, the requirement would be: There should be only one active email per customer, and each customer should not have duplicate emails" You can do that if you add a filtered unique index: create unique nonclustered index UX_Emails on dbo.Emails (CustomerID) where IsDefault = 1; Hope this helps you create a unique index SQL server!