Entity Framework Error: The Relationship Could Not be Changed Because One or More of the Foreign-key Properties is Non-nullable

Introduction

I am currently working on a MVC and Entity Framework based project. After a few days I encountered a problem with Entity Framework when trying to delete an entity from a related data collection and received the following error:

“The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.”

Suppose I have the following entity data model. In this model the MasterTable entity has many DetailTable records. Here there is a one-to-many relationship between MasterTable and DetailTable. Now I want to remove all DetailTable entities related to the MasterTable where the MasterId value is 1.

Table Relationship in Entity diagram

Table Definition and Test Data

CREATE TABLE [dbo].[MasterTable](
            [MasterId] [int] IDENTITY(1,1) NOT NULL,
            [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_MasterTable] PRIMARY KEY CLUSTERED
(
            [MasterId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DetailTable](
            [DetailId] [int] IDENTITY(1,1) NOT NULL,
            [MasterId] [int] NOT NULL,
            [Code] [varchar](50) NULL,            [Name] [varchar](50) NULL,
            [Description] [varchar](50) NULL,
 CONSTRAINT [PK_DetailTable] PRIMARY KEY CLUSTERED
(
            [DetailId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[DetailTable]  WITH CHECK ADD  CONSTRAINT [FK_DetailTable_MasterTable] FOREIGN KEY([MasterId])
REFERENCES [dbo].[MasterTable] ([MasterId])
GO
ALTER TABLE [dbo].[DetailTable] CHECK CONSTRAINT [FK_DetailTable_MasterTable]
GO
SET IDENTITY_INSERT [dbo].[MasterTable] O

INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (1, N'test1')
INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (2, N'test2')
INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (3, N'test3')

SET IDENTITY_INSERT [dbo].[MasterTable] OFF

SET IDENTITY_INSERT [dbo].[DetailTable] ON

INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (1, 1, N't1', N'name1', N'test')
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (2, 1, N't2', N'name2', N'test')
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (3, 1, N't3', N'name3', N'test')
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (4, 2, N't4', N'name4', N'test')
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (5, 2, N't5', N'name5', N'test')
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (6, 2, N't6', N'name6', N'test')
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (7, 3, N't7', N'name7', N'test')
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (8, 3, N't8', N'name8', N'test')
SET IDENTITY_INSERT [dbo].[DetailTable] OFF

Sample code to delete detail record from the master entity

 

static void Main(string[] args)
{
    using (Entities context = new Entities())
    {
        var masterData = context.MasterTables.Include("DetailTables").Where(p => p.MasterId == 1 ).FirstOrDefault();
        var childData = masterData.DetailTables.ToList();
        foreach (var data in childData)
        {
            masterData.DetailTables.Remove(data);
        }
        context.SaveChanges();
    }
}

 

Error snapshot

Resolution

We have two ways to resolve the issue.

1. The first solution is to delete the child object (entity) from the object context or DB context. When we delete a DetailTable, the Entity Framework will automatically detach the DetailTable entity from any of its relationships and the DetailTable is to be marked as a deletion. I need to make some small changes in my code. First I need to delete the child data from the Context.

static void Main(string[] args)
{
    using (Entities context = new Entities())
    {
        var masterData = context.MasterTables.Include("DetailTables").Where(p => p.MasterId == 1 ).FirstOrDefault();
        var childData = masterData.DetailTables.ToList();
        foreach (var data in childData)
        {
            context.DetailTables.DeleteObject(data);
        }
        context.SaveChanges();
    }
}

2. The second solution is a small change in the Primary Key of DetailTable. Here I include the Primary Key of the MasterTable (in other words MasterId) in a Primary Key of the detail table.

Update the model and my sample code works fine. 

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DetailTable]') AND name = N'PK_DetailTable')
ALTER TABLE [dbo].[DetailTable] DROP CONSTRAINT [PK_DetailTable]
GO

ALTER TABLE [dbo].[DetailTable] ADD  CONSTRAINT [PK_DetailTable] PRIMARY KEY CLUSTERED
(
        [DetailId] ASC,
        [MasterId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO