Why I get this kind of error The UPDATE statement conflicted with the FOREIGN KEY constraint

Question

I am trying to update one column from my table and when I press Update buttton I get error

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Tickets_AspNetUsers_UserId". The conflict occurred in database "CarPlatzz", table "dbo.AspNetUsers", column 'Id'. The statement has been terminated.

In my Model I have something like

public class Ticket
    {
        [Key]
        public int Id { get; set; }

        [Display(Name = "Opis")]
        public string Description { get; set; }

        [Display(Name = "Datum i vrijeme slanja")]
        public string DateAndTime { get; set; } = DateTime.Now.ToString("dd/MM/yyyy HH:mm");

        [Display(Name = "Datum i vrijeme zavrsetka")]
        public DateTime Answered { get; set; }

        [Display(Name = "Vrsta tiketa")]
        public int TicketTypeID { get; set; }

        [ForeignKey("TicketTypeID")]
        public virtual TicketType TicketType { get; set; }

        public int UserId { get; set; }

        [ForeignKey("UserId")]
        public virtual ApplicationUser ApplicationUser { get; set; }

        public int? ClientId { get; set; }
        [ForeignKey("ClientId")]
        public virtual Client Client { get; set; }

        public string Status { get; set; } = TicketStatus.Otvoren.ToString();


    }

And Here is my Action in Controller

 public IActionResult Upsert(TicketVM ticketVM)
        {
            var userName = User.FindFirstValue(ClaimTypes.Email);
            var user = HttpContext.User.Identity.Name;

            if (ModelState.IsValid)
            {
                if (ticketVM.Ticket.Id == 0)
                {
                    ticketVM.Ticket.ApplicationUser = _db.ApplicationUsers.FirstOrDefault(u => u.Email == userName);
                    ticketVM.Ticket.Status = TicketStatus.Otvoren.ToString();
                    _unitOfwork.Ticket.Add(ticketVM.Ticket);
                }
                else
                {
                    ticketVM.Ticket.Status = ((TicketStatus)Convert.ToInt32(ticketVM.Ticket.Status)).ToString();
                    _unitOfwork.Ticket.Update(ticketVM.Ticket);
                    
                }
                _unitOfwork.Save();
                return RedirectToAction(nameof(Index));
            }
            return View(ticketVM);
        }

I check a couple of post here but nothing helped me. So I change

public int UserId {get;set;} 

to

public int? UserId {get;set;}

But problem here is that after update column UserId in NULL which is not solution for me. So the problem is in my Upsert method in ELSE block

else
                {
                    ticketVM.Ticket.Status = ((TicketStatus)Convert.ToInt32(ticketVM.Ticket.Status)).ToString();
                    _unitOfwork.Ticket.Update(ticketVM.Ticket);
                    
                }

ApplicationUser.cs

public class ApplicationUser : IdentityUser<int>
    {
        
        [Required]
        [Display(Name = "Ime")]
        public string Name { get; set; }

        [Required]
        [Display(Name = "Adresa")]
        public string StreetAddress { get; set; }

        [Required]
        [Display(Name = "Grad")]
        public string City { get; set; }

        [Required]
        [Display(Name = "Postanski broj")]
        public string PostalCode { get; set; }

        public int? ClientId { get; set; }
        [ForeignKey("ClientId")]
        public Client Client { get; set; }

        [NotMapped]
        public string Role { get; set; }      

        [NotMapped]
        public IEnumerable<SelectListItem> RoleList { get; set; }

        [NotMapped]
        public IEnumerable<SelectListItem> ClientList { get; set; }
    }

REFERENCE is here

LINK 1

LINK 2

LINK 3

LINK 4

Answer 1

I can see a couple serious issues with your approach. The primary culprit that sets off alarms is this:

ticketVM.Ticket.ApplicationUser = _db.ApplicationUsers.FirstOrDefault(u => u.Email == userName);

followed by:

_unitOfwork.Ticket.Add(ticketVM.Ticket);

Why are you using a _unitOfWork, but then going to some _db reference which looks like a direct DbContext. A Unit of Work wraps a DbContext instance so all entities should be resolved from that single DbContext. I would expect to see something like:

ticketVM.Ticket.ApplicationUser = _unitOfWork.ApplicationUsers.Single(u => u.Email == userName);

If the ApplicationUser is being pulled from a different DbContext instance than the Ticket is being persisted to, that second DbContext won't be tracking the instance and would result in potentially trying to insert a duplicate User.

Based on the insert or update nature of the method, you would want the setting of the ApplicationUser reference to only occur on the Insert scenario so that the user reference doesn't change on an update unless that is what is supposed to happen.

The second warning flag is the use of FirstOrDefault. This method really needs a restriction in Linq to NOT function without an OrderBy like the Skip and Take methods. If you expect a record back it is better to use Single which will throw an exception if it doesn't find an expected matching row. With FirstOrDefault you may get a null reference if the e-mail matching doesn't line up and that would attempt to remove a user reference from the ticket.

The next recommendation would be to avoid mixing entities and view models. Entities represent data state. View models represent view state. Serializing entities into a view model means sending more data than the view actually needs, and can lead to performance issues when a serializer starts "touching" navigation properties tripping lazy loads. Keep view models as POCOs and project the entity values needed into the view model properties. Serializing an Identity User and potentially it's associated details is almost certainly something you don't want to be sending over the wire to a web client.

Statements like:

_unitOfwork.Ticket.Update(ticketVM.Ticket);

are a high risk for data tampering within a system. While your UI may only present some expected fields that a user might modify, the ENTIRE entity is open to modification when using this approach. When updating data, load the entity from data state and copy across the validated details from the view model then save the entity.

Answer 2

The error message is self-explanatory. It seems that your application tried to update a row in the table and the value did not exist in the referenced table, which broke the referential integrity and caused the error.