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