ASP.NET

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 Relationship

Table Definition and Test Data

  1. CREATE TABLE [dbo].[MasterTable](  
  2.             [MasterId] [int] IDENTITY(1,1) NOT NULL,  
  3.             [Name] [varchar](50) NOT NULL,  
  4.  CONSTRAINT [PK_MasterTable] PRIMARY KEY CLUSTERED  
  5. (  
  6.             [MasterId] ASC  
  7. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  8. ) ON [PRIMARY]  
  9. GO  
  10. CREATE TABLE [dbo].[DetailTable](  
  11.             [DetailId] [int] IDENTITY(1,1) NOT NULL,  
  12.             [MasterId] [int] NOT NULL,  
  13.             [Code] [varchar](50) NULL,            [Name] [varchar](50) NULL,  
  14.             [Description] [varchar](50) NULL,  
  15.  CONSTRAINT [PK_DetailTable] PRIMARY KEY CLUSTERED  
  16. (  
  17.             [DetailId] ASC  
  18. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  19. ) ON [PRIMARY]  
  20. GO  
  21. SET ANSI_PADDING OFF  
  22. GO  
  23.    
  24. ALTER TABLE [dbo].[DetailTable]  WITH CHECK ADD  CONSTRAINT [FK_DetailTable_MasterTable] FOREIGN KEY([MasterId])  
  25. REFERENCES [dbo].[MasterTable] ([MasterId])  
  26. GO  
  27. ALTER TABLE [dbo].[DetailTable] CHECK CONSTRAINT [FK_DetailTable_MasterTable]  
  28. GO   
  29. SET IDENTITY_INSERT [dbo].[MasterTable] O  
  30.   
  31. INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (1, N'test1')  
  32. INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (2, N'test2')  
  33. INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (3, N'test3')  
  34.   
  35. SET IDENTITY_INSERT [dbo].[MasterTable] OFF  
  36.    
  37. SET IDENTITY_INSERT [dbo].[DetailTable] ON  
  38.   
  39. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (1, 1, N't1', N'name1', N'test')  
  40. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (2, 1, N't2', N'name2', N'test')  
  41. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (3, 1, N't3', N'name3', N'test')  
  42. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (4, 2, N't4', N'name4', N'test')  
  43. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (5, 2, N't5', N'name5', N'test')  
  44. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (6, 2, N't6', N'name6', N'test')  
  45. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (7, 3, N't7', N'name7', N'test')  
  46. INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (8, 3, N't8', N'name8', N'test')  
  47. SET IDENTITY_INSERT [dbo].[DetailTable] OFF
Sample code to delete detail record from the master entity

 

  1. static void Main(string[] args)  
  2. {  
  3.     using (Entities context = new Entities())  
  4.     {  
  5.         var masterData = context.MasterTables.Include("DetailTables").Where(p => p.MasterId == 1 ).FirstOrDefault();  
  6.         var childData = masterData.DetailTables.ToList();  
  7.         foreach (var data in childData)  
  8.         {  
  9.             masterData.DetailTables.Remove(data);  
  10.         }  
  11.         context.SaveChanges();  
  12.     }  
  13. }  

 

Error snapshot

error

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.

  1. static void Main(string[] args)  
  2. {  
  3.     using (Entities context = new Entities())  
  4.     {  
  5.         var masterData = context.MasterTables.Include("DetailTables").Where(p => p.MasterId == 1 ).FirstOrDefault();  
  6.         var childData = masterData.DetailTables.ToList();  
  7.         foreach (var data in childData)  
  8.         {  
  9.             context.DetailTables.DeleteObject(data);  
  10.         }  
  11.         context.SaveChanges();  
  12.     }  
  13. }

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. 

  1. IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DetailTable]') AND name = N'PK_DetailTable')  
  2. ALTER TABLE [dbo].[DetailTable] DROP CONSTRAINT [PK_DetailTable]  
  3. GO  
  4.   
  5. ALTER TABLE [dbo].[DetailTable] ADD  CONSTRAINT [PK_DetailTable] PRIMARY KEY CLUSTERED  
  6. (  
  7.         [DetailId] ASC,  
  8.         [MasterId] ASC  
  9. )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]  
  10. GO  
Error resolve 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found