CODEWITHSUNDEEP
sqlsql-servert-sqlazure-sql-databaseunique-constraint
Paul Fleming
Paul Fleming

Reputation: 24526

SQL Server Conditional Unique Index

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.

Upvotes: 7

Views: 1681

Answers (3)

i-one
i-one

Reputation: 5120

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

Upvotes: 2

Adrian Cornwell
Adrian Cornwell

Reputation: 196

how about adding an on insert/update trigger that performs the checks and raises a fail exception if the rule violated. you could add these without changing the data model.

Upvotes: 0

asafrob
asafrob

Reputation: 1858

If I got the question right I would add another table to link between SetID and Title which have a 1:1 relation and add a unique index on ClientID and SetID

CREATE UNIQUE INDEX [Index_Name]
ON [dbo].[MyTable]([ClientID ], [SetID])

Upvotes: 0

Related Questions