Say I have a Customers table, and an Emails table. Emails table has these fields:


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?

  • Why not normalize the emails in a new table and place a unique constraint on composite index of CustomerId and EmailId? Or would that just return you to the issue of ensuring unique emails, e.g. the value is entered by the customer?
    – dartonw
    Mar 11, 2015 at 6:14
  • @dartonw, that's feasible. But I guess in my case that's over-normalization. Thanks for the solution anyway. Mar 11, 2015 at 6:38

1 Answer 1


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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.