Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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      Null

because "" 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;
share|improve this question
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. – ypercubeᵀᴹ Aug 14 '13 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. – Jack Douglas Aug 14 '13 at 10:26
up vote 4 down vote accepted

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, '', ''),
       (2, '', '')

This statement fails

UPDATE UserProfile
SET PrimaryEmail = CASE Id WHEN 1 THEN '' WHEN 2 THEN '' END,
    SecondaryEmail = CASE Id WHEN 1 THEN '' WHEN 2 THEN '' 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.

share|improve this answer
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 at 21:43

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.

share|improve this answer

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).

share|improve this answer

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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