CODEWITHSUNDEEP
c#.netsql-serverentity-frameworkentity-framework-6
Riz
Riz

Reputation: 6686

How to define a multi-level unique index constraint in EF6

EF allows us to define unique index constraint on multiple properties like so:

public class Part 
{
        public int Id { get; set; }
        [Index("IX_Name_Factory", Order = 1, IsUnique = true)]
        public string Name {get;set;}
        [Required]
        public Factory Factory { get; set; }
        [ForeignKey("Factory"), Index("IX_Name_Factory", Order = 2, IsUnique = true)]
        public int Factory_Id {get;set;}
}

This allows me to have a part with a name that's unique for a given Factory. But what if I have multiple factories per region, and for some reason I want to have a part with a name that unique across a region, not just a factory. The structure would be Region -> Factory -> Part. How would I define a unique index for such a condition since Region is not a direct property of Part?

Edit: since it seems that this may not be possible to define in EF6, I am open to doing it in sql server directly. Can somebody please let me know how I can do this in sql server?

Upvotes: 1

Views: 226

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89266

I want to have a part with a name that unique across a region, not just a factory. . . [and] ensure that Part.Factory.Region.Id is always same as Part.Region.Id no matter what?

You make the key of Factory (RegionID,FactoryID), and the key of Part (RegionID,FactoryID,PartID). So Part refers to Factory using a Foreign Key of (RegionID,FactoryID).

That will ensure that a Part's RegionID is always the same as the Part's Factory's RegionID.

Then to make a Part.Name unique within a Region, add a Unique index on Part (RegionID,Name).

Working in SQL Server (or Azure SQL Database) directly, you can create a unique index on an indexed view, and also avoid having a RegionID column on Part altogether. EG:

use tempdb

go
drop table if exists Part
drop table if exists Factory
drop table if exists Region 
go

create table Region(RegionID int primary key);
create table Factory(FactoryID int primary key, RegionID int references Region);
create table Part(PartID int primary key, FactoryID int references Factory, Name varchar(200));

go

create view vRegionPartName
with schemabinding
as
select r.RegionID, p.Name PartName
from dbo.Region r
join dbo.Factory f
  on r.RegionID = f.RegionID
join dbo.Part p 
  on p.FactoryID = f.FactoryID

go

create unique clustered index pk_vRegionPartName
on vRegionPartName(RegionID,PartName)

go

insert into Region(RegionID) values (1)
insert into Factory(FactoryID,RegionID) values (1,1)
insert into Factory(FactoryID,RegionID) values (2,1)
insert into Part(PartID,FactoryID,Name) values (1,1,'Part1')

insert into Part(PartID,FactoryID,Name) values (2,2,'Part1')
--fails with
--Msg 2601, Level 14, State 1, Line 36
--Cannot insert duplicate key row in object 'dbo.vRegionPartName' with unique index 'pk_vRegionPartName'. The duplicate key value is (1, Part1).

Upvotes: 1

ErroneousFatality
ErroneousFatality

Reputation: 476

What you're trying to achieve means that the Part entity logically has to know about the Region it belongs to, because it's uniqueness is defined by it. So why not just make Part have a reference to Region too, and use that relation's key as a part of your unique key too?

Upvotes: 1

Related Questions