clear markdown help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 400790 distinct fiddles created so far.

CREATE TABLE dbo.Example ( ID integer IDENTITY NOT NULL PRIMARY KEY, UserName nvarchar(10) NOT NULL, Gateway varchar(10) NOT NULL, Code tinyint NOT NULL, ActiveTo date NULL, OrgId integer NOT NULL ); INSERT dbo.Example (UserName, Gateway, Code, ActiveTo, OrgId) VALUES (N'user1', 'gateway ', 50, NULL, 100), (N'user1', 'gateway ', 50, NULL, 101), (N'user2', 'gateway2', 51, NULL, 102), (N'user3', 'gateway3', 52, '20120817', 103);
4 rows affected
4 rows affected
 hidden batch(es)


-- New column ALTER TABLE dbo.Example ADD IgnoreThisDuplicate integer NULL;
 hidden batch(es)


-- Run once to identify duplicates to ignore 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 ); -- This constraint is not trusted -- Will only check new rows -- i.e. New rows must have NULL IgnoreThisDuplicate ALTER TABLE dbo.Example WITH NOCHECK ADD CONSTRAINT CHK_Example_NoNewDuplicates CHECK (IgnoreThisDuplicate IS NULL);
1 rows affected
1 rows affected
 hidden batch(es)


-- Applying the constraint to the table above would ignore the two user1 duplicates. CREATE UNIQUE INDEX UNQ_Example_UniqueNewUsernameGatewayCode ON dbo.Example (Username, Gateway, Code, IgnoreThisDuplicate) WHERE ActiveTo IS NULL;
 hidden batch(es)


SELECT * FROM dbo.Example;
ID UserName Gateway Code ActiveTo OrgId IgnoreThisDuplicate
1 user1 gateway 50 100
2 user1 gateway 50 101 2
3 user2 gateway2 51 102
4 user3 gateway3 52 17/08/2012 00:00:00 103
ID UserName Gateway Code ActiveTo OrgId IgnoreThisDuplicate
1 user1 gateway 50 100
2 user1 gateway 50 101 2
3 user2 gateway2 51 102
4 user3 gateway3 52 17/08/2012 00:00:00 103
 hidden batch(es)


--Adding a row [user2], [gateway2], [52], [Null] would be fine. INSERT dbo.Example (UserName, Gateway, Code, ActiveTo, OrgId) VALUES (N'user2', 'gateway2', 52, NULL, 104);
1 rows affected
1 rows affected
 hidden batch(es)


-- Adding a row [user2], [gateway2], [51], [Null] would then throw an error. INSERT dbo.Example (UserName, Gateway, Code, ActiveTo, OrgId) VALUES (N'user2', 'gateway2', 51, NULL, 104);
Msg 2601 Level 14 State 1 Line 2 Cannot insert duplicate key row in object 'dbo.Example' with unique index 'UNQ_Example_UniqueNewUsernameGatewayCode'. The duplicate key value is (user2, gateway2, 51, <NULL>). Msg 3621 Level 0 State 0 Line 2 The statement has been terminated.
Msg 2601 Level 14 State 1 Line 2 Cannot insert duplicate key row in object 'dbo.Example' with unique index 'UNQ_Example_UniqueNewUsernameGatewayCode'. The duplicate key value is (user2, gateway2, 51, <NULL>). Msg 3621 Level 0 State 0 Line 2 The statement has been terminated.
 hidden batch(es)


-- Then adding a row [user3], [gateway3], [52], [Null] would be fine. INSERT dbo.Example (UserName, Gateway, Code, ActiveTo, OrgId) VALUES (N'user3', 'gateway3', 52, NULL, 105);
1 rows affected
1 rows affected
 hidden batch(es)