Sql-server – Unique constraint across two columns

constraintsql server

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;

Best Answer

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.

Related Question