data/authors/Paul Logan.json

SQL Unique filtered index

Software 166 words One minute
Contents

Our factory dashboard displays one key stage per factory on the main view.

The key stage is determined by a bit column in the Stages database table, which was previously updated using raw SQL by a developer - until I CRUD’ed it up.

Now, managers could update this through a web app.

A recent helpdesk ticket reported that there was a duplicate stage shown on the dashboard.

A manager was able to tick another stage to be primary when one already existed for a factory.

I wanted to ensure that sets of stages in the table with the same FactoryID could only have one stage set to primary.

A UNIQUE INDEX with a WHERE filter predicate was the way to go:

CREATE UNIQUE INDEX [UQ_Primary_Stage_Per_Factory] ON [dbo].Stage
(
	FactoryID ASC,
	IsPrimary ASC
)
WHERE (IsPrimary=(1))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SQL