SQL - Unique key across 2 columns of same table?

Question

I use SQL Server 2016. I have a database table called "Member".

In that table, I have these 3 columns (for the purpose of my question):

• idMember [INT - Identity - Primary Key]
• memEmail
• memEmailPartner

I want to prevent a row to use an email that already exists in the table.

Both email columns are not mandatory, so they can be left blank (NULL).

If I create a new Member:

If not blank, the values entered for "memEmail" and "memEmailPartner" (independently) should not be found in any other rows in columns memEmail nor memEmailPartner.

So if I want to create a row with email ([email protected]) I must not find any occurrences of that value in memEmail or memEmailPartner.

If I update an existing Member:

I must not find any occurrences of that value in memEmail or memEmailPartner, with the exception that I am updating the row (idMembre) which already have the value in memEmail or memEmailPartner.

--

From what I read on Google, it should be possible to do something with a Function-Based Check Constraint but I can't make that work.

Anyone have a solution to my problem ?

Thank you.

Answer 1

TL;DR

The wisdom of applying this kind of business rule logic in the database needs to be reconsidered - this check is likely a better candidate for your application, or a stored procedure which acts as an insert gate keeper instead of direct new row inserts into the table.

Ignoring the Warnings

That said, I do believe that what you want is however possible in a constraint UDF, albeit with potentially atrocious performance consequences^*1, and likely prone to race conditions in set based updates

Here's a user defined function which applies the unique email logic across both columns. Note that by the time the constraint is checked, that the row is IN the table already, hence the new row itself needs to be excluded from the duplicate checks.

My code also is depedent on ANSI NULL behaviour, i.e. that the predicates NULL = NULL and X IN (NULL) both return NULL, and hence are excluded from the failure check (in order to meet your requirement that NULLS do not fail the rule).

We also need to check for the insert of BOTH new columns being non-null, but duplicated.

So here's the a UDF doing the checking:

CREATE FUNCTION dbo.CheckUniqueEmails(@id int, @memEmail varchar(50), 
                                      @memEmailPartner varchar(50))
RETURNS bit
AS 
BEGIN
   DECLARE @retval bit;
   IF @memEmail = @memEmailPartner
     OR EXISTS (SELECT 1 FROM MyTable WHERE memEmail IS NOT NULL 
                AND memEmail IN(@memEmail, @memEmailPartner) AND idMember <> @id)
     OR EXISTS (SELECT 1 FROM MyTable WHERE memEmailPartner IS NOT NULL 
                AND memEmailPartner IN(@memEmail, @memEmailPartner) AND idMember <> @id)
     SET @retval = 0
   ELSE 
     SET @retval = 1;
  RETURN @retval;
END;
GO

Which is then enforced in a CHECK constraint:

ALTER TABLE MyTable ADD CHECK (dbo.CheckUniqueEmails(
                                       idMember, memEmail, memEmailPartner) = 1);

I've put a SQLFiddle up here

Uncomment the 'failed' test cases to ensure that the above check constraint is working.

I haven't tested this with updates, and as per Martin's advice on the link, this will likely break on an insert with multiple rows.

^*1 - we'll need indexes on BOTH email address columns.

Answer 2

A trigger is the traditional way of doing doing what you're asking for. Here's a simple demo;

--if object_id('member') is not null drop table member
go

create table member (
    idMember INT Identity Primary Key,
    memEmail varchar(100),
    memEmailPartner varchar(100) 
)
go

create trigger trg_member on member after insert, update as
begin
    set nocount on

    if exists (select 1 from member m join inserted i on i.memEmail = m.memEmail and i.idMember <> m.idMember) or
       exists (select 1 from member m join inserted i on i.memEmail = m.memEmailPartner and i.idMember <> m.idMember) or
       exists (select 1 from member m join inserted i on i.memEmailPartner = m.memEmail and i.idMember <> m.idMember) or
       exists (select 1 from member m join inserted i on i.memEmailPartner = m.memEmailPartner and i.idMember <> m.idMember) 
    begin
        raiserror('Email addresses must be unique.', 16, 1)
        rollback
    end 
end
go

insert member(memEmail, memEmailPartner) values('[email protected]', null), ('[email protected]', null), (null, '[email protected]'), (null, '[email protected]')
go

select * from member

insert member(memEmail, memEmailPartner) values('[email protected]', null) -- should fail
go
insert member(memEmail, memEmailPartner) values(null, '[email protected]') -- should fail
go
insert member(memEmail, memEmailPartner) values('[email protected]', null) -- should fail
go
insert member(memEmail, memEmailPartner) values(null, '[email protected]') -- should fail
go

insert member(memEmail, memEmailPartner) values('[email protected]', null) -- should work
go
insert member(memEmail, memEmailPartner) values(null, '[email protected]') -- should work
go

select * from member

-- Make sure updates still work!
update member set memEmail = memEmail, memEmailPartner = memEmailPartner

I've not tested this extensively but it should be enough to get you started if you want to try this approach.

StuartLC notes the potential for the UDF check constraint to fail in set based updates and/or various other conditions, triggers don't have this problem.

Stuart also suggests reconsidering whether this should really be a database constraint or managed through business logic elsewhere. I'm inclined to agree - my gut feel here is that sooner or later you will come across a situation that requires email addresses to be reused, or in some other way not strictly unique.

Answer 3

I may have misunderstood exactly what you were asking but it looks like you want a simple upsert query with IF EXISTS conditions.

DECLARE @emailAddress VARCHAR(255)= '[email protected]', --dummy value
        @id INT= 2;                                      --dummy value

IF NOT EXISTS
(
   SELECT 1
   FROM @Member
   WHERE memEmail = @emailAddress
         OR memEmailPartner = @emailAddress
)
    BEGIN
        SELECT 'insert';
    END;
ELSE IF EXISTS 
(
  SELECT 1
  FROM @Member
  WHERE idMember = @id
)
    BEGIN
       SELECT 'update';
    END;