How To Create A Unique Constraint With Conditional Filter

Recently I had a request which on the surface sounded a little bit tricky, however there ended up being a simple solution thanks to filtered indexes.

The request was to create a unique constraint, but it only aplied when a certain column had a certain value.

I didn’t want to slow things down with a bunch of checks going on everytime a record was inserted so did some research and found that creating a unique filtered index would do the job.

Using a Filtered Index to Create Unique Constraint With Argument

As always a simple demo speaks more to a DBA than a bunch of explanations so here is an example.

This table will have a list of 3 products, shared amoung stores.

Every store has a list of all the products however only one store can sell one product at a time.

The products are shoes, hats and socks. The stores are store 1,2 and 3.

If store 1 is selling socks, store 2 can only sell hats or shoes.

You get the gist – lets get to work.


USE DBAFire_DB

CREATE TABLE #UniqueFilteredIndex (id int, storeno smallint, Product varchar (24), AvailableForSale Bit )

INSERT INTO #UniqueFilteredIndex
SELECT 1,1,'Shoes',1
UNION ALL
SELECT 2,1,'Socks',0
UNION ALL
SELECT 3,1,'Hats',0
UNION ALL
SELECT 4,2,'Socks',1
UNION ALL
SELECT 5,2,'Shoes',0
UNION ALL
SELECT 6,2,'Hats',0
UNION ALL
SELECT 7,3,'Shoes',0
UNION ALL
SELECT 8,3,'Socks',0
UNION ALL
SELECT 9,3,'Hats',1


SELECT * FROM #UniqueFilteredIndex
--Create Unique Filtered Index

CREATE UNIQUE INDEX ix_example 
ON #UniqueFilteredIndex(Product,AvailableForSale)
WHERE AvailableForSale = 1

--------------------------------------
-- Now try to update store 3 to also sell shoes

UPDATE  #UniqueFilteredIndex
SET AvailableForSale = 1 WHERE storeno = 3 and Product = 'Shoes'

----------------------------------------------------------------

You will note from playing around with this that the unique constraint is only applied to those products which are available for sale.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.