Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

Looking for suggestions on what table constraints to have to achieve uniqueness

on Wed Dec 13, 2017 11:15 am

Expecting data like below in a sql-server table:

A resource with id1 will entries for different versions and can also have different names for different versions.

But Name cannot be shared among resources. Once id1 use NameX, other resource should not be able to use same name.

Please suggest sql-table constraints I can define to achieve this:
Code:
Id  Name    Version
------------------
id1  Name1  1
id1  Name1  2
id1  NameA  3
id1  NameX  4
id2  Name2  1
id2  NameX  2      --invalid record, NameX is already used for id1

Answers
You can use an indexed view with a couple of unique indexes to ensure that each name only appears once per id value in the view and then to make the complete set of names unique:
Code:
create table dbo.Ix (ID varchar(20) not null, Name varchar(20) not null,
                    Version int not null)
go
create view dbo.DRI_Ix_Unique_Names
with schemabinding
as
    select
        Id,Name,COUNT_BIG(*) as Cnt
    from
        dbo.Ix
    group by
        ID,Name
go
create unique clustered index IX_DRI_IX_Unique_Names on dbo.DRI_Ix_Unique_Names (Id,Name)
go
create unique nonclustered index IX_DRI_IX_Unique_Names_Only on
        dbo.DRI_Ix_Unique_Names(Name)
go
insert into dbo.Ix(ID,Name,Version) values
('id1','Name1',1)
go
insert into dbo.Ix(ID,Name,Version) values
('id1','Name1',2)
go
insert into dbo.Ix(ID,Name,Version) values
('id1','NameA',3)
go
insert into dbo.Ix(ID,Name,Version) values
('id1','NameX',4)
go
insert into dbo.Ix(ID,Name,Version) values
('id2','Name2',1)
go
insert into dbo.Ix(ID,Name,Version) values
('id2','NameX',2)

This results in five successful inserts followed by an error because the final insert violates the nonclustered unique index.

I'm not sure how the version column factors into your requirements and am not using it in any of the constraints.
Back to top
Permissions in this forum:
You can reply to topics in this forum