# Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column.

I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.

Can I create a UNIQUE that doesn't check for existing compliance?

I have tried using NOCHECK but was unsuccessful.

In this case I have a table which ties licensing information to "CompanyName"

EDIT: Having multiple rows with the same "CompanyName" is bad data, but we can't remove or update those duplicates at this time. One approach is to have the INSERTs use a stored procedure which will fail for duplicates... If it was possible to have SQL check the uniqueness on its own, that would be preferable.

This data is queried by company name. For the few existing duplicates this will mean that multiple rows are returned and displayed... While this is wrong, it's acceptable in our use case. The goal is to prevent it in the future. It seems to me from the comments that I have to do this logic in the stored procedures.

-

## migrated from stackoverflow.comJun 4 '13 at 19:50

This question came from our site for professional and enthusiast programmers.

You could use an insert/update trigger. You can't use a unique constraint. If it can't guarantee the column values are unique, what's the value? –  Amy Jun 4 '13 at 17:48
You can't apply a constraint only to new rows - and if the constraint doesn't check there isn't much point to having it there at all. Maybe you should consider using a filtered unique index (say, WHERE identity_column > <current value>;) or a new column with WHERE value IS NOT NULL;). –  Aaron Bertrand Jun 4 '13 at 17:49
I think the NOCHECK option is valid for FOREIGN KEY and CHECK constraints. Not possible for UNIQUE and PRIMARY KEY ones to be not checked or disabled. –  ypercube Jun 4 '13 at 17:55
Are you allowed to change the table (add one more column)? –  ypercube Jun 4 '13 at 20:17
@ypercube unfortunately not. –  Matthew Jun 4 '13 at 20:46

The answer is "yes". You can do this with a filtered index (see here for documentation).

For instance, you can do:

create unique index t_col on t(col) where id > 1000;


This creates a unique index, only on new rows, rather than on the old rows. This particular formulation would allow duplicates with existing values.

If you have just a handful of duplicates, you could do something like:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);

-
Whether or not that's good would depend upon whether "old" existing items should prevent the creation of new items with the same value. –  supercat Jun 4 '13 at 18:34
@supercat . . . I gave an alternative formulation for building the index on everything except existing duplicate values. –  Gordon Linoff Jun 4 '13 at 18:35
For the latter to work, one would have to ensure that one omitted from the list one id for each distinct key value that had duplicates, and would also have to ensure that if the item which was deliberately omitted from the list got removed from the table, an item with an equal key would get removed from the list. –  supercat Jun 4 '13 at 18:44
@supercat . . . I agree. Keeping the index consistent for updates and deletes is all the more challenging because you can't re-create the index in a trigger. In any case, I had the impression from the OP that the data -- or at least the duplicates -- are not changing often, if at all. –  Gordon Linoff Jun 4 '13 at 18:53
Basically I would create the rule with an explicit exception for my existing duplicates? –  Matthew Jun 4 '13 at 20:02

Yes you can do that.

Here is a table with duplicates:

CREATE TABLE dbo.Party
(
ID INT NOT NULL
IDENTITY ,
CONSTRAINT PK_Party PRIMARY KEY ( ID ) ,
Name VARCHAR(30) NOT NULL
) ;
GO

INSERT  INTO dbo.Party
( Name )
VALUES  ( 'Frodo Baggins' ),
( 'Luke Skywalker' ),
( 'Luke Skywalker' ),
( 'Harry Potter' ) ;
GO


Let us ignore existing ones, and ensure that no new duplicates can be added:

ALTER TABLE dbo.Party ADD IgnoreThisDuplicate INT NULL ;
GO

UPDATE  dbo.Party
SET     IgnoreThisDuplicate = ID
WHERE   EXISTS ( SELECT *
FROM   dbo.Party AS p1
WHERE  p1.Name = dbo.Party.Name
AND p1.ID < dbo.Party.ID ) ;
GO

-- this constraint is not trusted
ALTER TABLE dbo.Party WITH NOCHECK
CHECK(IgnoreThisDuplicate IS NULL);
GO

SELECT * FROM dbo.Party;
GO

CREATE UNIQUE INDEX UNQ_Party_UniqueNewNames ON dbo.Party(Name, IgnoreThisDuplicate);
GO


Let us test this solution:

-- cannot add a name that exists
INSERT  INTO dbo.Party
( Name )
VALUES  ( 'Frodo Baggins' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

-- cannot add a name that exists and has an ignored duplicate
INSERT  INTO dbo.Party
( Name )
VALUES  ( 'Luke Skywalker' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

-- can add a new name
INSERT  INTO dbo.Party
( Name )
VALUES  ( 'Hamlet' );

-- but only once
INSERT  INTO dbo.Party
( Name )
VALUES  ( 'Hamlet' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

-
Except he can't add a column to the table. –  Aaron Bertrand Jun 4 '13 at 21:09
I like how this answer turns how NULL values are treated in non-standard way in unique constraint into something useful. Cunning trick. –  ypercube Jun 4 '13 at 21:33
@ypercube thank you! –  A-K Jun 5 '13 at 13:08

The filtered unique index is a brilliant idea but it has a minor disadvantage - no matter if you use the WHERE identity_column > <current value> condition or the WHERE identity_column NOT IN (<list of ids for duplicate values here>).

With the first approach , you will still be able to insert duplicate data in the future, duplicates of existing (now) data. For example, if you have (even only one) row now with CompanyName = 'Software Inc.', the index will not forbid the insertion of one more row with same company name. It will only forbid it if you try twice.

With the second approach there is an improvement, the above will not work (which is good.) However, you will still be able to insert more duplicates or existing duplicates. For example, if you have (two or more) rows now with CompanyName = 'DoubleData Co.', the index will not forbid the insertion of one more row with same company name. It will only forbid it if you try twice.

(Update) This can be corrected if for every duplicate name, you keep out of the exclusion list one id. If, like the above example, there are 4 rows with duplicate CompanyName = DoubleData Co. and IDs 4,6,8,9, the exclusion list should have only 3 of these IDs.

With the second approach another disadvantage is the cumbersome condition (how much cumbersome depends on how many duplicates there are in the first place), since SQL-Server seems to not support the NOT IN operator in the WHERE part of filtered indexes. See SQL-Fiddle. Instead of WHERE (CompanyID NOT IN (3,7,4,6,8,9)), you'll have to have something like WHERE (CompanyID <> 3 AND CompanyID <> 7 AND CompanyID <> 4 AND CompanyID <> 6 AND CompanyID <> 8 AND CompanyID <> 9) I'm not sure if there are efficiency implications with such a condition, if you have hundreds of duplicate names.

Another solution (similar to @Alex Kuznetsov's) is to add another column, populate it with rank numbers and add a unique index including this column:

ALTER TABLE Company
ADD Rn TINYINT DEFAULT 1;

UPDATE x
SET Rn = Rnk
FROM
( SELECT
CompanyID,
Rn,
Rnk = ROW_NUMBER() OVER (PARTITION BY CompanyName
ORDER BY CompanyID)
FROM Company
) x ;

CREATE UNIQUE INDEX CompanyName_UQ
ON Company (CompanyName, Rn) ;


Then, inserting a row with duplicate name will fail because of the DEFAULT 1 property and the unique index. This is still not 100% foolproof (while Alex's is). Duplicates will still slip in if the Rn is explicitly set in the INSERT statement or if the Rn values are maliciously updated.

SQL-Fiddle-2

-
This is a brilliant answer, plus this words even with mysql as it does not have conditional indexes –  monotheist Nov 9 '14 at 7:21

Another alternative is to write a scalar function that checks if a value already exists in the table and then call that function from a check constraint.

This will do horrible things to performance.

-