Moved MVC3 database to remote server. Can no longer DELETE.

Question

I created a simple MVC3 project just like the Music Store sample. Everything worked just fine. It was then time to port the database to a GoDaddy server and now I can't delete items from the database.

This is the error:

The DELETE statement conflicted with the REFERENCE constraint \"Captions_Item_Captions\". The conflict occurred in database \"mattymattmofo\", table \"dbo.Captions\", column 'Item_ItemId'.\r\nThe statement has been terminated.

    //
    // POST: /NavigationManager/Delete/5

    [Authorize(Roles = "Administrator")]
    [HttpPost, ActionName("Delete")]
    public ActionResult DeleteConfirmed(int id)
    {
        Item item = db.Items.Find(id);
        db.Items.Remove(item);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

Can anyone please help?

score 3 · Answer 1 · answered May 25 '12 at 08:20

3

There is something wrong with your foreign key. The problem is in database. You can't delete row that is bind with foreign key

answered May 25 '12 at 08:20

karaxuna

26,752
13
82
117

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

In your database Items (parent table) is linked with Captions (child table) by foreing key. That means when you are removing a row from Items table there still might be a connection to this row in Captions table. So constraint is generated to preserve database integrity. You could do two things to resolve this:

Remove constraint - not recommended and bad practice
Update constraint with Delete Rule. You have following choices: No Action, Cascade, Set Null, Set Default.

Right now your constraint delete rule is probably set to No Action and that's why you are having this error.

You can set Delete Rule in Management Studio by right clicking on constraint (in Keys folder) -> Modify -> extend INSERT And UPDATE Spcecification -> Delete Rule.

Or by T-SQL script. There are many examples on how to do it.

edited May 23 '17 at 12:04

Community

1
1

answered May 25 '12 at 09:07

lucask

2,290
24
19

I am using GoDaddy to host on a shared server. When I try to access this it says "You are not logged on as the database owner or system administrator." and won't let me change anything. Is there another way around this? – Matt May 25 '12 at 15:36
Are you sure you are not logging in as a read-only database user? In GoDaddy administration panel you should find proper sql server user and password. I'm not familiar with GoDaddy services though. Maybe you could run SQL script that would recreate foreing key constraint in their admin panel? – lucask May 25 '12 at 16:38

Moved MVC3 database to remote server. Can no longer DELETE.

2 Answers2