MVC - ALTER TABLE DROP COLUMN failed because one or more objects access this column

Question

I get the following error message from Package Manager Console when trying to remove the refrence to a class:

The object 'FK_dbo.Resultats_dbo.Ovelses_OvelseId' is dependent on column 'OvelseId'. ALTER TABLE DROP COLUMN OvelseId failed because one or more objects access this column.

I get this problem when doing the following changes to my model

    public class Resultater
{
    public int Id { get; set; }

    [Required]
    public string AspNetUsersId { get; set; } //foreign key for users

    //public Ovelser Ovelse { get; set; }

    //[Required]
    //public int OvelseId { get; set; }

    [Required]
    [Display(Name = "Dato")]
    public DateTime Date { get; set; }

    [Required]
    public string Form { get; set; }

}

public class Ovelser
{
    public int Id { get; set; }

    [Required]
    [Display(Name = "Øvelse")]
    public OvelseType OvelseType { get; set; }

    [Required]
    public short OvelseTypeId { get; set; }

    [Required]
    public decimal Resultat { get; set; }

    [Required]
    [Display(Name = "Hvordan var utførelsen")]
    public string Beskrivelse { get; set; }

}

Package Manager Console gives me the following code when I start a migration:

    public override void Up()
    {
        DropForeignKey("dbo.Resultaters", "OvelseId", "dbo.Ovelsers");
        DropIndex("dbo.Resultaters", new[] { "OvelseId" });
        DropColumn("dbo.Resultaters", "OvelseId");
    }

    public override void Down()
    {
        AddColumn("dbo.Resultaters", "OvelseId", c => c.Int(nullable: false));
        CreateIndex("dbo.Resultaters", "OvelseId");
        AddForeignKey("dbo.Resultaters", "OvelseId", "dbo.Ovelsers", "Id", cascadeDelete: true);
    }

I found a very similar problem "ALTER TABLE DROP COLUMN failed because one or more objects access this column" but I am unable to apply it.

Strange problem! Is your `Resultater` related to another class via `Ovelse_Id`? — TanvirArjel, Mar 20 '19 at 12:57
I will remake the the error message and and the comitt as I allredy removed "OvelseId" — Sondre, Mar 20 '19 at 12:59
Why are you unable to apply the answer from the similar problem? — David Specht, Mar 20 '19 at 12:59
i don't know how I do "drop constraint" as the intellisense isn't picking up on it... — Sondre, Mar 20 '19 at 13:05
@TanvirArjel Yes the Resultater is related to the "Ovelse_Id" as when I previously removed OvelseId it was created (found out after you made your previous question) (I edited my question as it is now my entire change) — Sondre, Mar 20 '19 at 13:11
Can you look in the Object Explorer in SQL Server Management Studio at your table `Resultater`? Click on the plus sign next to it and then the plus for Keys and also check Constraints. That should give you the name of the constraint you need to drop. — David Specht, Mar 20 '19 at 13:12
@Sondre Found nothing wrong in your provided code. Can you give remote access so that I can check it properly? — TanvirArjel, Mar 20 '19 at 13:13
@TanvirArjel I believe that the problem is that `OvelseId` is not properly deleted before `Ovelse` is being removed. I have upploaded my project to [link]https://www.dropbox.com/s/dyfb5fqgu5p3s3h/Kast.zip?dl=0. I am not sure how to give remote access as my project is stored locally (only team viewer) I would be grateful for solving this problem! — Sondre, Mar 20 '19 at 13:26
Have you tried un-commenting `public int OvelseId { get; set; }` and just removing `public Ovelser Ovelse { get; set; }` first? — David Specht, Mar 20 '19 at 13:52
@David Specht Tried it now, got to delete `Ovelse`, but not `OvelseId` afterwards `The object 'FK_dbo.Resultats_dbo.Ovelses_OvelseId' is dependent on column 'OvelseId'` — Sondre, Mar 20 '19 at 14:09
I'm not sure why EF isn't dropping the foreign key for you. The code `DropForeignKey("dbo.Resultaters", "OvelseId", "dbo.Ovelsers");` should do exactly that. My only other suggestion is the answer I gave, where you would need to use SQL in a separate program. — David Specht, Mar 20 '19 at 14:37
I was looking at DropForeignKey(). There is another version that allows you to specify the name of the foreign key. I wonder if that might work. `DropForeignKey("dbo.Resultaters", "FK_dbo.Resultats_dbo.Ovelses_OvelseId");` https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.dbmigration.dropforeignkey?view=entity-framework-6.2.0#System_Data_Entity_Migrations_DbMigration_DropForeignKey_System_String_System_String_System_Object_ — David Specht, Mar 20 '19 at 14:46
@David Specht Added that line under the first `DropForeignKey` and it WORKS! Post that as an answer so I can accept your glorious solution! — Sondre, Mar 20 '19 at 21:09

score 3 · Accepted Answer · answered Mar 21 '19 at 13:00

3

I was looking at DropForeignKey(). There is another version that allows you to specify the name of the foreign key. You might be able to add that to your migration. Microsoft document on DropForeignKey

DropForeignKey("dbo.Resultaters", "FK_dbo.Resultats_dbo.Ovelses_OvelseId");

answered Mar 21 '19 at 13:00

David Specht

7,784
1
22
30

I am capable of deleting with this, but want I want to add the foreign key in `Ovelser` `public override void Up() { CreateIndex("dbo.Ovelsers", "ResultaterId"); DropForeignKey("dbo.Resultaters", "FK_dbo.Ovelsers_dbo.Resultaters_ResultaterId"); }` I get the following error from Package Manager Console... `The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Ovelsers_dbo.Resultaters_ResultaterId". The conflict occurred in database "aspnet-Kast-20190310120456", table "dbo.Resultaters", column 'Id'.` – Sondre Mar 21 '19 at 20:20
That looks strange. If you are adding a foreign key to Ovelser, the up() method should look more like the down() method you posted in your original question except with the table names switched. The error message makes it look like there is already a foreign key ResultaterId in the Ovelser table. – David Specht Mar 22 '19 at 01:59
oh my GOD .. saved my night mate, thank you very much, cheers. maintaining legacy software is a nightmare, don't do it, lmao! – Nexus Mar 14 '20 at 18:34

David Specht · Answer 2 · 2019-03-20T13:45:23.933

0

Try this in SQL Server Management Studio and then do your migration.

use [Name Of Your Database];

alter table Resultater drop constraint [FK_dbo.Resultats_dbo.Ovelses_OvelseId];

edited Mar 20 '19 at 13:45

answered Mar 20 '19 at 13:17

David Specht

7,784
1
22
30

You would need to implement it in a program that lets you directly access your database, like Sql Server Management Studio. Select "New Query" and put `use [Name Of Your Database];` at the top and then the alter table statement. You can't use it in your program in Visual Studio. – David Specht Mar 20 '19 at 13:40

MVC - ALTER TABLE DROP COLUMN failed because one or more objects access this column

2 Answers2