Sql-server – SQL Server unique index, per subset of data, based on values of another column

filtered-indexsql serverunique-constraint

Say I have a Customers table, and an Emails table. 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:

  1. Creating a function to ensure uniqueness of each new email value, and call that function in a check constraint
  2. Create a trigger, and on each insert or update ensure that the email is not duplicate per customer
  3. Forget about 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?

Best Answer

Create a unique 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;
Related Question