Add UNIQUE constraint WITH NOCHECK ? Not really …

Ever wanted to add a UNIQUE constraint WITH NOCHECK ? Maybe you had some duplicates in your data, fixed the code, but you cannot remove the duplicates, or maybe it’s a “temporary” thing.

Well, you cannot have a UNIQUE constraint WITH NOCHECK, because these are actually unique indexes and these need to be checked. I had this problem where 2 columns were suppose to be a unique combination, and after quite some years, discovered that, who would’ve guessed … there are duplicates. We couldn’t just rush to delete data and add an unique constraint but we had to stop duplicates being added.
Having this table with data populated:

CREATE TABLE duplicates (
	id INT IDENTITY(1,1) PRIMARY KEY,
	value_a INT,
	value_b INT,
	specific_value VARCHAR(10)
)    
GO

INSERT INTO duplicates (value_a, value_b, specific_value)
VALUES	(1,2,'abc'),
	(1,2,'xyz')
GO

when I tried to add the unique constraint with nocheck on columns value_a and value_b:

ALTER TABLE duplicates WITH NOCHECK 
	ADD CONSTRAINT UQ_duplicates UNIQUE (value_a, value_b)
GO

boom:

Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.duplicates' and the index name 'UQ_duplicates'. The duplicate key value is (1, 2).
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.

Ok, the workaround is a scalar function which counts the values that I need to be distinct – in my case value_a and value_b:

CREATE FUNCTION [dbo].[UQ_duplicates_count](@value_a INT, @value_b INT)
RETURNS INT
AS 
BEGIN
	DECLARE	@ret_value INT 

	SELECT	@ret_value = COUNT(1) 
	FROM	duplicates
	WHERE	value_a = @value_a
		AND value_b = @value_b

	RETURN @ret_value
END
GO

and add a CHECK constraint, WITH NOCHECK, that checks the function doesn’t return a value greater than 1:

ALTER TABLE duplicates WITH NOCHECK 
	ADD CONSTRAINT UQ_duplicates_with_function 
				CHECK (dbo.UQ_duplicates_count(value_a, value_b) <= 1)
GO

This way, i dont need to delete all duplicates right away, and I can evaluate the 3rd column in my table – specific_value – to see which one is correct and remove the duplicate.

And now, if I try to add another duplicate:

INSERT INTO duplicates (value_a, value_b, specific_value)
VALUES	(1,2,'def')

I’m getting the right boom:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "UQ_duplicates_with_function". The conflict occurred in database "TestDb", table "dbo.duplicates".
The statement has been terminated.

I said above that UNIQUE constraints are indexes – let’s look at this. Let’s say we fixed our problem with duplicates, removed the CHECK constraint using the function and we add the right unique constraint:

TRUNCATE TABLE duplicates /* cough*/
GO
ALTER TABLE duplicates
	DROP CONSTRAINT UQ_duplicates_with_function 
GO
ALTER TABLE duplicates
	ADD CONSTRAINT UQ_duplicates UNIQUE (value_a, value_b)
GO

When I check sys.indexes, I can find an entry there for my unique constraint, a NONCLUSTERED index:

SELECT * FROM sys.indexes WHERE name = 'UQ_duplicates'

object_id	name		index_id	type	type_desc	...
437576597	UQ_duplicates	4		2		NONCLUSTERED	...

Further, I insert a bunch of rows in this table:

INSERT INTO duplicates (value_a, value_b)
VALUES (CHECKSUM(NEWID()), CHECKSUM(NEWID()))
GO 10000

And here’s the query plan generated for following query:

SELECT	value_a 
FROM	duplicates 
WHERE	value_a > 10000

unique constraint index
My unique constraint is an index and I can see an INDEX SEEK operator on it.

This approach, with using a function in a check constraint, can be handy in many scenarios, not just this one, for example to validate complicated input or to validate data against other tables.

  1. another dude

    thanks dude, your work to publish this is very much appreciated, it helped me a LOT.

  2. Clever.
    Have you tested this for performance on a large table?
    Thank you!

Reply to Alex ¬
Cancel reply