CODEWITHSUNDEEP
c#asp.netasp.net-mvcentity-frameworkasp.net-mvc-4
user7629010
user7629010

Reputation:

DELETE statement conflicted (ASP.NET MVC)

I have ASP.NET MVC app

I have two relative tables Companies and Vacancies.

When I delete Company, I want to delete relative to it Vacancies.

Here is my controller

  public ActionResult Delete(int? id)
    {
        if (id == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }
        Company companies = db.Companies.Find(id);
        if (companies == null)
        {
            return HttpNotFound();
        }
        return View(companies);
    }

    // POST: Companies/Delete/5
    [HttpPost, ActionName("Delete")]
    [ValidateAntiForgeryToken]
    public ActionResult DeleteConfirmed(int id)
    {
        Company companies = db.Companies.Find(id);
        db.Companies.Remove(companies);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

And here is Companies table

CREATE TABLE [dbo].[Companies] (
[CompanyID]   INT            IDENTITY (1, 1) NOT NULL,
[CompanyName] NVARCHAR (MAX) NULL,
PRIMARY KEY CLUSTERED ([CompanyID] ASC)

);

Anв vacancies 

CREATE TABLE [dbo].[Vacancies] (
[VacancyId]   INT        IDENTITY (1, 1) NOT NULL,
[VacancyName] NCHAR (10) NULL,
[CompanyID]   INT        NULL,
PRIMARY KEY CLUSTERED ([VacancyId] ASC),
CONSTRAINT [FK_Vacancies_ToTable] FOREIGN KEY ([CompanyID]) REFERENCES [dbo].[Companies] ([CompanyID])

);

How I need to modify my syntax to easily delete company?

Upvotes: 2

Views: 80

Answers (1)

johnny 5
johnny 5

Reputation: 20995

Modify your dependent to add ON DELETE CASCADE

CREATE TABLE [dbo].[Vacancies] (
    [VacancyId]   INT        IDENTITY (1, 1) NOT NULL,
    [VacancyName] NCHAR (10) NULL,
    [CompanyID]   INT        NULL,
    PRIMARY KEY CLUSTERED ([VacancyId] ASC),
    CONSTRAINT [FK_Vacancies_ToTable] 
    FOREIGN KEY ([CompanyID]) 
    REFERENCES [dbo].[Companies] ([CompanyID])
    ON DELETE CASCADE);

This will allow delete your references when you delete you're company. Alternatively you can mark each entity as deleted from C#, this will give you more control and avoid accidental deletes

Upvotes: 1

Related Questions