I need to add a constraint with two columns that says if any given value is present in one of the columns, then:
1) It cannot be duplicated in the same column.
2) It cannot be duplicated in the other column either.

The constraint we are looking to make is with PrimaryEmail & SecondaryEmail.

This would be invalid:

UserId    PrimaryEmail       SecondaryEmail
231       [email protected]      Null
424       [email protected]      [email protected]

because "[email protected]" is present in the first column and therefore it cannot be present in the second column regardless of what row it's in.

Is it possible to define this type of constraint in SQL Server 2008?

We started by defining a table just for emails, but we've since reverted from that model in favor of two hard columns for many reasons including: query speed, query complexity, and the probability of a user using multiple email accounts actively decreases in order of magnitude after one.

This defines a traditional two column constraint but its on a per row basis between the two columns and doesn't give us what we are after:

ON UserProfile (PrimaryEmail, SecondaryEmail)
WHERE PrimaryEmail IS NOT NULL and SecondaryEmail IS NOT NULL;
  • 2
    I don't see how a table with emails would lead to query complexity. The unique constraint woudl be trivially enforced and you know that you could still have two columns in this table, as foreign keys to that table, if you want to limit the users to max of 2 emails. Aug 14, 2013 at 8:33
  • Query speed: really? databases were born to join. What performance testing results have left you thinking query speed will be a problem? Query complexity: that's a complete red herring, just create a view if you need to. Aug 14, 2013 at 10:26

4 Answers 4


This isn't possible with the proposed table structure declaratively. You would need triggers to enforce this.

A unique index on both columns, together with a pair of check constraints with scalar UDFs, gets quite close however.

     Id             INT PRIMARY KEY,
     PrimaryEmail   VARCHAR(100),
     SecondaryEmail VARCHAR(100)

  ON UserProfile(PrimaryEmail)

  ON UserProfile(SecondaryEmail)


CREATE FUNCTION dbo.EmailInUseAsPrimary (@Email VARCHAR(100))
        (SELECT COUNT(*)
         WHERE  PrimaryEmail = @Email)


CREATE FUNCTION dbo.EmailInUseAsSecondary (@Email VARCHAR(100))
        (SELECT COUNT(*)
         WHERE  SecondaryEmail = @Email)


  ADD CHECK ( dbo.EmailInUseAsPrimary(SecondaryEmail) = 0), 
      CHECK ( dbo.EmailInUseAsSecondary(PrimaryEmail) = 0)

The reason for READCOMMITTEDLOCK is to avoid problems with snapshot isolation.

One problem with the approach above is that because the constraints are evaluated RBAR it can fail some transactions that ought to succeed.

For the example data

VALUES (1, '[email protected]', '[email protected]'),
       (2, '[email protected]', '[email protected]')

This statement fails

UPDATE UserProfile
SET PrimaryEmail = CASE Id WHEN 1 THEN '[email protected]' WHEN 2 THEN '[email protected]' END,
    SecondaryEmail = CASE Id WHEN 1 THEN '[email protected]' WHEN 2 THEN '[email protected]' END  

even though at the end of the transaction the constraints would have been met. But maybe it is sufficiently unlikely that you will be performing this kind of update (swapping email addresses between both type and person) that this can be ignored.

  • excellent point on the failing statement, it is because the functions run row by row and we need the entire set, I am thinking in INSTEAD OF triggers on insert and update so we have the entire set that we want to validate.
    – Alex
    Mar 16, 2016 at 21:43
  • Top answer!! Thank you. It may be better practice in the functions to use CASE WHEN EXISTS (...) THEN 1 ELSE 0 END, because the SELECT can stop once any row is found. Of course that'll only make it quicker if it fails, not if it passes, so probably not worth the effort. Nov 22, 2018 at 13:35

Depending on what you are modelling I may see a problem for this restriction anyway: for some of our clients the secondary contact address for some people is a shared inbox for the whole team - so we contact the shared box if there is no response (or an out-of-office reply) to messages sent to the personal one.

The only way to enforce the desired restriction declaratively, without using programmed logic such as the trigger method described by Martin, is to treat email addresses as separate entities instead of just properties of a person as you described you had already considered:

Person         EmailAddress    
--------       ----------------
pID (PK)  <--  pID (FK) (PK)   
Name           Type     (PK)   
Blah           Address (unique)

The primary key over person ID and address type enforces (as a primary key implies a unique index) each person having at most one address of each type (primary, secondary) and the unique index on EmailAddress.Address enforces no two people having the same address no matter what address type.

query complexity

This can be addressed using views. You are right that this might introduce "query speed" differences but I'm pretty sure they would not be significant.

query speed

If this is a significant problem then you could still have primary and secondary address columns in the Person table, and maintain them in using triggers on the EmailAddress table.

You then need to chose what happens if a dev/user tries to update the addresses in the Person table directly: you can either use a trigger to update EmailAddress accordingly, or a trigger to raise an error if the user tries to set values for those columns directly - though in both cases you are trading off insert and update performance for the select speed/convenience. Of course you are now using programmed logic to control things so the complexity is similar to Martin's suggestion.

Another option is indexed views, though I think they are only available on Enterprise Edition which may rule out their use depending on your project's size.

This may all be moot, because as I said above: I doubt this structure would make a significant performance difference. I expect it would require a massive data-set for the difference to be reliably measurable, never mind noticeable by end users.

and the probability of a user using multiple email accounts actively decreases in order of magnitude after one

As long as you don't care about that one user who has many and will play merry hell if he can't record them all! Though having said that most systems only accept the possibility of one relevant address anyway.


This type of validation/constraint is extremely expensive given the fact that you have to compare against the entire table over and over again, also you cannot accomplish this through unique constraints, Martin Smith's answer above gets you close (I commented about something closer to that but very slow of course).

What you can do is to create an EAV (Entity Attribute Value) Architecture:


dbo.UserProfile_Email(identity_col, id as FK to UserProfile, emailType, email )

where emailType is 1 or 2 (integers that specify if primary or secondary) and the email,

then you can safely put a unique constraint on the email, and each new user gets two inserts on User_Profile_Email (all wrapped in a single transaction in case one fails).


Have you tried checks which evaluate a function - see the example in the link below


Apologises if I've got the wrong end of the stick

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.