Take the 2-minute tour ×
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. It's 100% free, no registration required.

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       joe@yahoo.com      Null
424       smo@gmail.com      joey@yahoo.com

because "joe@yahoo.com" 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:

CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueEmail_notnull
ON UserProfile (PrimaryEmail, SecondaryEmail)
WHERE PrimaryEmail IS NOT NULL and SecondaryEmail IS NOT NULL;
share|improve this question
1  
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
add comment

3 Answers

up vote 2 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.

CREATE TABLE UserProfile
  (
     Id             INT PRIMARY KEY,
     PrimaryEmail   VARCHAR(100),
     SecondaryEmail VARCHAR(100)
  )

CREATE UNIQUE INDEX IX1
  ON UserProfile(PrimaryEmail)

CREATE UNIQUE INDEX IX2
  ON UserProfile(SecondaryEmail)

go

CREATE FUNCTION dbo.EmailInUseAsPrimary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  PrimaryEmail = @Email)
  END;

go

CREATE FUNCTION dbo.EmailInUseAsSecondary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  SecondaryEmail = @Email)
  END;

GO

ALTER TABLE UserProfile
  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

INSERT INTO UserProfile
VALUES (1, 'abc@abc.com', 'def@def.com'),
       (2, 'ghi@ghi.com', 'jkl@jkl.com')

This statement fails

UPDATE UserProfile
SET PrimaryEmail = CASE Id WHEN 1 THEN 'jkl@jkl.com' WHEN 2 THEN 'def@def.com' END,
    SecondaryEmail = CASE Id WHEN 1 THEN 'ghi@ghi.com' WHEN 2 THEN 'abc@abc.com' 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
add comment

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
add comment

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

http://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx

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

share|improve this answer
add comment

Your Answer

 
discard

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.