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?

  • 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 '15 at 6:14
  • @dartonw, that's feasible. But I guess in my case that's over-normalization. Thanks for the solution anyway. – Saeed Neamati Mar 11 '15 at 6:38
up vote 4 down vote accepted

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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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