SQL Server Conditional Unique Index

azure-sql-database, sql, sql-server, t-sql, unique-constraint

Solution

You may try additional indexed view.

For example, a table:

create table dbo.Test (PK int, ClientID int, SetID int, Title varchar(50), primary key (PK))

insert into dbo.Test values
    (1, 1, 1, 'Title1')
    ,(2, 1, 1, 'Title1')
    ,(3, 2, 2, 'Title1')
    ,(4, 2, 2, 'Title1')
    ,(5, 1, 3, 'Title2')
    ,(6, 1, 3, 'Title2')

The view and index:

create view dbo.vTest
with schemabinding
as
    select ClientID, Title, SetID, cnt=count_big(*)
    from dbo.Test
    group by ClientID, Title, SetID
GO
create unique clustered index UX_vTest on dbo.vTest (ClientID, Title)
GO

Then:

insert into dbo.Test values (7, 1, 1, 'Title1') -- will pass
insert into dbo.Test values (8, 1, 1, 'Title1') -- will pass
insert into dbo.Test values (9, 1, 2, 'Title1') -- will fail
insert into dbo.Test values (10, 2, 2, 'Title1') -- will pass
insert into dbo.Test values (11, 1, 3, 'Title1') -- will fail

Problem

Given the following table (and sample data): ``` PK | ClientID | SetID | Title ----------------------------- P1 | C1 | S1 | Title1 P2 | C1 | S1 | Title1 P3 | C2 | S2 | Title1 P4 | C2 | S2 | Title1 P3 | C1 | S3 | Title2 P5 | C1 | S3 | Title2 ``` Assuming a `Set` belongs to a `Client`, can I have a unique index that constraints the title being unique within a client except with it's siblings within the same set. So for example, I can have `Title1` in two `Clients` but not twice in one `Client`. Now for `Client1`, I want to have a second record with `Title1` but only when it has the same `SetID` as all others with `Title`. Just to note, I'm using SQL Azure, but I'm interested more generally (e.g 2008 R2/2012) too. Edit: Please note that I cannot change the structure of the table. It exists this way already, and has a complex business layer behind it. If I can fix this, as is, then great, if not, then I can leave it broken.

Original source