Конфликт инструкции update с ограничением foreign key

Recently I tried to add a one-to-many relationship in my MVC application via Entity Framework Code First. I added the relationship to bind an Administrator name from a dropdown list to the current application that is being filled out. So I have one table for the administrator names and one for the actual application information. The application and dropdown list of admin names seem to work fine and all information is going into my database on submit, but when I try to Edit the application, I get the following error:

The UPDATE statement conflicted with the FOREIGN KEY constraint The conflict occurred in database table «dbo.Administrator», column ‘AdministratorId’

I’ve tried setting my Id columns to «Not Null», but this did not solve the issue.

Model:

public class Administrator
{

    public int AdministratorId{ get; set; }
    public string AdministratorName{ get; set; }

}

public class Application
{
    public Application()
    {
        GetDate = DateTime.Now;
    }

    public int ApplicationId { get; set; }

    [DisplayName("Marital Status")]
    public bool? MaritalStatus { get; set; }

    [Required]
    [DisplayName("First Name")]
    public string FirstName { get; set; }

    [DisplayName("Middle Initial")]
    public string MiddleInitial { get; set; }
     [Required]
    [DisplayName("Last Name")]
    public string LastName { get; set; }

    [DisplayName("Date Submitted")]
    public DateTime GetDate { get; set; }

    public int AdministratorId{ get; set; }

    public virtual Administrator Administrator{ get; set; }
}

Controller (Index):

 public ActionResult Index()
    {
        ViewBag.LoanOfficerId = new SelectList(db.Administrators, "AdministratorId", "AdministratorName");
        return View();
    }

    // POST: Applications/Create

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Index([Bind(Include = "AdministratorId,FirstName,MiddleInitial,LastName,")] Application application)
    {



        if (ModelState.IsValid)
        {
ViewBag.AdministratorId= new SelectList(db.Administrators, "AdministratorId",     "AdministratorName", application.Administrator);
            db.Applications.Add(application);
            db.SaveChanges();

            return RedirectToAction("Thanks");
        }


        return View(application);
    }

When I am trying to save Updated data in table using Entity Framework in ASP.NET MVC, I am getting the below error

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_CustomerProfileV2_IndustryList". 
The conflict occurred in database "CRM", table "dbo.IndustryList", column 'IndustryName'.
The statement has been terminated.

How can i Resolve the above issue?

Here is my Current sample code

 [HttpPost]
        public ActionResult EditCustomer(long customerProfileID, CustomerProfile model)
        {
            try
            {
                //code removed 

                crmdb.Entry(model).State = System.Data.Entity.EntityState.Modified;
                crmdb.SaveChanges(); //error here

               //some code removed
            }
            catch (Exception ex)
            {
                Response.Write(ex.ToString().Replace("n", "<br>"));
                return PartialView();
            }
        }

Any help is appreciated, thanks

2 Answers

ALTER TABLE YourTable
DROP Constraint Your_FK
GO

ALTER TABLE YourTable
ADD CONSTRAINT [New_FK_Constraint]
FOREIGN KEY (YourColumn) REFERENCES ReferencedTable(YourColumn)
ON DELETE CASCADE ON UPDATE CASCADE
GO 

Scenario:

You are working as SQL Server developer, You wrote an update statement for one of the table and getting below error.

Msg 547, Level 16, State 0, Line 32
The UPDATE statement conflicted with the REFERENCE constraint «FK_». 
The conflict occurred in database «YourDatabaseName», table «SchemaName.YourTableName», column ‘ColumnName’.
The statement has been terminated.

How to resolve this issue?

Solution:

Let’s create this error first by using below script. We are going to create two tables dbo.Customer and dbo.Orders. The tables has Primary-Foreign Key Relationship.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES Customer(CustomerId)
    )


    --insert sample data
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

    insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,1)

How to update record when Column is referenced by Foreign Key Constraint in SQL Server

Now let’s say if you feel that CustomerId value is incorrect in dbo.Customer and need to be updated. You wrote below update statement to update CustomerId to 100.

    update dbo.Customer
    set Customerid=100

As there is no Customer_id value=100 in dbo.Orders table, You can’t update the record in reference table. Now you thought that let’s fix the Parent table first ( dbo.Orders) and then I can update the dbo.Customer table.

    update dbo.Orders
    set Customer_Id=100

Again you got the error as shown below, because we don’t have CustomerId=100 available in dbo.Customer table.

From here we can come with with multiple solutions

1) Instead of updating the record, Insert the record in Reference Table ( Dbo.Customer), Then update the record in Parent table (Dbo.Orders) and finally delete the existing records from Reference Table.

    --Insert Record in Reference Table First
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (100,'Aamir','Shahzad','000-000-00')

    --Update the Records in Parent Table 
        update dbo.Orders
    set Customer_Id=100

    --Delete the old record from Reference Table
    Delete from dbo.Customer
    where CustomerId=1

Check the records in table now.

How to update Column Value when referenced by Foreign Key Constraint in SQL Server

2) Disable the Foreign Key Constraint and Update the Values Manually

Another solution can be, disable the Foreign Key constraint, update the records and finally enable the Foreign key again.

--Find the Foreign Key Constraint with Table Name
    USE YourDatabaseName
    GO
    Select 
    Schema_name(Schema_id) as SchemaName,
    object_name(Parent_object_id) as TableName,
    name as ForeignKeyConstraintName
    from sys.foreign_keys

Disable the Foreign Key Constraint by using below statement

Syntax

I used below statement to disable Foreign Key constraint on dbo.Orders table.

--Disable Foregin Key by using NOCHECK
ALTER TABLE dbo.Orders
NOCHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E

--Run Update Statements
    update dbo.Customer
    set Customerid=100

    update dbo.Orders
    set Customer_Id=100

Enable Foreign Key Constraint Syntax

I execute below script to Enable Foreign Key Constraint on dbo.Orders table.

--Enable Foreign Key Constraint by using CHECK
ALTER TABLE dbo.Orders
CHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E