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?

  • If "old" users can belong to several organizations you might consider creating a new table CREATE TABLE ADDITIONAL_USER_ORGANISATIONS (Username ..., OrgId ..., primary key ( Username, OrgId )); and move violating rows there. – Lennart Aug 13 '17 at 11:05

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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