SQL Conditional Unique Constraint With Where Clause Within Same Table

conditional-statements, sql, sql-server, sql-server-2008-r2, unique-constraint

Solution

As workaround, You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.

CREATE UNIQUE NONCLUSTERED INDEX [IX__MyTable.MFG.Model.Class.Depiction.Iteration] 
ON [dbo].[MyTable] ([ManufacturerID],[Model],[BlockClassID],[BlockDepictionID],[BlockIterationID])
WHERE [Flag] = 0;

TechNet article

Problem

I have a table where I want to ensure that a combination of five columns remain unique within that table. For example: ``` ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [UQ__MyTable.MFG.Model.Class.Depiction.Iteration] UNIQUE NONCLUSTERED ( [ManufacturerID] ASC, [Model] ASC, [BlockClassID] ASC, [BlockDepictionID] ASC, [BlockIterationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ``` I want to exclude combinations where a sixth separate column has a particular value. For example, I only want to enforce this above constraint when the column [Flag] = 0 and exclude enforcement when the column [Flag] = 1 .

Original source