The DELETE statement conflicted with the REFERENCE constraint "FK__tbl8_update__HID__55F4C372"

Question

I have the following 2 tables

CREATE TABLE HOLIDAY_DATE_TABLE
(
HID INT IDENTITY PRIMARY KEY,
TABLE_NUMBER nchar(2) NOT NULL,
HOLIDAY_DATE nchar(8) NOT NULL,
FIELD_DESCRIPTION nVARchar(43) NULL,
);


CREATE TABLE tbl8_update_transactions
(
TID INT IDENTITY PRIMARY KEY,
TABLE_NUMBER nchar(2) NOT NULL,
HOLIDAY_DATE nchar(8) NOT NULL,
FIELD_DESCRIPTION nVARchar(43) NULL,
HID int,
FOREIGN KEY (HID) REFERENCES HOLIDAY_DATE_TABLE (HID)
);

When I perform a delete via my application view (Delete.cshtml), I get the following error:

"The DELETE statement conflicted with the REFERENCE constraint "FK__tbl8_update__HID__55F4C372". The conflict occurred in database "BillingUI", table "tbl8_update_transactions", column 'HID'. The statement has been terminated."

Basically, when a delete is performed on a particular row from my view, I want it to delete the same record in both HOLIDAY_DATE_TABLE and tbl8_update_transactions. I read that I have to delete the child and then the parent. I tried changing the order in which the deletes take place, however when I do this it deletes the wrong record from tbl8_update_transactions because it's looking at its primary key for identification and not its foreign key. My code is below, thanks.

 public ActionResult Delete(int id = 0)
    {
        HOLIDAY_DATE_TABLE holiday_date_table = db.HOLIDAY_DATE_TABLE.Find(id);
        //tbl8_update_transactions tbl8_update_transaction = db.tbl8_update_transactions.Find(id);
        if (holiday_date_table == null)
        {
            return HttpNotFound();
        }
        return View(holiday_date_table);
    }

    //
    // POST: /Table8/Delete/5

    [HttpPost, ActionName("Delete")]
    public ActionResult DeleteConfirmed(int id)
    {
        HOLIDAY_DATE_TABLE holiday_date_table = db.HOLIDAY_DATE_TABLE.Find(id);
        db.HOLIDAY_DATE_TABLE.Remove(holiday_date_table);
        db.SaveChanges();
        tbl8_update_transactions tbl8_update_transaction = db.tbl8_update_transactions.Find(id);
        db.tbl8_update_transactions.Remove(tbl8_update_transaction);
        db.SaveChanges();
        return RedirectToAction("../Billing/HolidayDateTable");
    } 

Update: I think I wasn't clear enough. I tried reversing them already, i.e;

public ActionResult Delete(int id = 0)
    {
        HOLIDAY_DATE_TABLE holiday_date_table = db.HOLIDAY_DATE_TABLE.Find(id);
        //tbl8_update_transactions tbl8_update_transaction = db.tbl8_update_transactions.Find(id);
        if (holiday_date_table == null)
        {
            return HttpNotFound();
        }
        return View(holiday_date_table);
    }

    //
    // POST: /Table8/Delete/5

    [HttpPost, ActionName("Delete")]
    public ActionResult DeleteConfirmed(int id)
    {          
        tbl8_update_transactions tbl8_update_transaction = db.tbl8_update_transactions.Find(id);
        db.tbl8_update_transactions.Remove(tbl8_update_transaction);
        db.SaveChanges();
        HOLIDAY_DATE_TABLE holiday_date_table = db.HOLIDAY_DATE_TABLE.Find(id);
        db.HOLIDAY_DATE_TABLE.Remove(holiday_date_table);
        db.SaveChanges();
        return RedirectToAction("../Billing/HolidayDateTable");
    }

but it doesn't work, I receive the error "Value cannot be null. Parameter name: entity", pointing to db.tbl8_update_transactions.Remove(tbl8_update_transaction);

This is because (at least I think) that it's deleting from tbl8_update_transaction where it's unique identifier TID is equal to the value of HID in HOLIDAY_DATE_TABLE. I need it to delete from tbl8_update_transaction using its foreign key value and am not sure how in C#.

Update 2: I tried having it identify the row by the HID (foreign key) value in tbl8_update_transaction...

 public ActionResult Delete(int id = 0, int HID = 0)
    {
        HOLIDAY_DATE_TABLE holiday_date_table = db.HOLIDAY_DATE_TABLE.Find(id);
        tbl8_update_transactions tbl8_update_transaction = db.tbl8_update_transactions.Find(HID);
        if (holiday_date_table == null)
        {
            return HttpNotFound();
        }
        return View(holiday_date_table);
    }

    //
    // POST: /Table8/Delete/5

    [HttpPost, ActionName("Delete")]
    public ActionResult DeleteConfirmed(int id, int HID)
    {          
        tbl8_update_transactions tbl8_update_transaction = db.tbl8_update_transactions.Find(HID);
        db.tbl8_update_transactions.Remove(tbl8_update_transaction);
        db.SaveChanges();
        HOLIDAY_DATE_TABLE holiday_date_table = db.HOLIDAY_DATE_TABLE.Find(id);
        db.HOLIDAY_DATE_TABLE.Remove(holiday_date_table);
        db.SaveChanges();
        return RedirectToAction("../Billing/HolidayDateTable");
    }

But get the following error: The parameters dictionary contains a null entry for parameter 'HID' of non-nullable type 'System.Int32' for method 'System.Web.Mvc.ActionResult DeleteConfirmed(Int32, Int32)' in 'BillingApp.Controllers.Table8Controller'. An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter. Parameter name: parameters

Answer 1

Try using ON DELETE CASCADE on the child table's Foreign Key Constraint.

CREATE TABLE tbl8_update_transactions
(
TID INT IDENTITY PRIMARY KEY,
TABLE_NUMBER nchar(2) NOT NULL,
HOLIDAY_DATE nchar(8) NOT NULL,
FIELD_DESCRIPTION nVARchar(43) NULL,
HID int,
FOREIGN KEY (HID) REFERENCES HOLIDAY_DATE_TABLE (HID) ON DELETE CASCADE
);

The ON DELETE CASCADE implements a cascading delete on the parent table's rows which will delete the associated child table's rows when a parent table's row is deleted.

Answer 2

You should delete all the objects where this ID is Foreign key. They will be not valid if you delete this reference, because of that you have this exception.

Answer 3

You need to flip your Remove methods around. You are deleting the parent first when there is still a child. You need to first delete the row from tbl8_update_transaction and then holiday_date_table.

tbl8_update_transactions tbl8_update_transaction = db.tbl8_update_transactions.Find(id);
db.tbl8_update_transactions.Remove(tbl8_update_transaction);
db.SaveChanges();
HOLIDAY_DATE_TABLE holiday_date_table = db.HOLIDAY_DATE_TABLE.Find(id);
db.HOLIDAY_DATE_TABLE.Remove(holiday_date_table);
db.SaveChanges();