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

.net c# entity-framework entity-framework-6 sql-server

Question

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?

1
1
2/4/2019 10:02:46 PM

Popular Answer

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).
1
2/4/2019 10:13:49 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow