Constraint on existing table - unique combination of values only when another column is null

Question

I have a pre-existing table structured similarly to this:

[Username]   [Gateway]   [Code]   [ActiveTo]   [OrgId]  
=======================================================
user1        gateway      50        Null        100
user1        gateway      50        Null        101
user2        gateway2     51        Null        102
user3        gateway3     52       12/08/17     103

This table already has duplicate combinations of [Username], [Gateway], and [Code].

I want to add a constraint such that any new users added have a unique combination of [Username], [Gateway], and [Code] but that ignores two pre-existing duplicates such as the two user1 entries above (not ignoring new entries that match pre-existing ones).

I only want this to be applied when the duplicate's [ActiveTo] value is null (new entries have ActiveTo as null by default). So:

• Applying the constraint to the table above would ignore the two user1 duplicates.
• Adding a row [user2], [gateway2], [52], [Null] would be fine.
• Adding a row [user2], [gateway2], [51], [Null] would then throw an error.
• Then adding a row [user3], [gateway3], [52], [Null] would be fine.

Any ideas?

Answer 1

Blatantly copied from A-K's answer in the similar question (Can I add a unique constraint that ignores existing violations?) and adjusted for this one.

It assumes there is an ID column with a unique constraint:

ALTER TABLE dbo.Example
ADD IgnoreThisDuplicate integer NULL;
GO
UPDATE E
SET IgnoreThisDuplicate = ID
FROM dbo.Example AS E
WHERE EXISTS 
( 
    SELECT *
    FROM dbo.Example AS P1
    WHERE 
        P1.Username = E.Username
        AND P1.Gateway = E.Gateway
        AND P1.Code = E.Code
        AND P1.ActiveTo IS NULL 
        AND E.ActiveTo IS NULL
        AND P1.ID < E.ID
);
GO
-- This constraint is not trusted
ALTER TABLE dbo.Example 
WITH NOCHECK
    ADD CONSTRAINT CHK_Example_NoNewDuplicates 
    CHECK (IgnoreThisDuplicate IS NULL);
GO
-- Applying the constraint to the table above would ignore the two user1 duplicates.
GO
SELECT * FROM dbo.Example;
GO
CREATE UNIQUE INDEX 
    UNQ_Example_UniqueNewUsernameGatewayCode 
ON dbo.Example
    (Username, Gateway, Code, IgnoreThisDuplicate)
WHERE 
    ActiveTo IS NULL;

Test it out: db<>fiddle demo