One developer's SQL blog

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

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.

Twitter

Linkedin

Facebook