CODEWITHSUNDEEP
sqlentity-framework-6
Woot
Woot

Reputation: 1809

Renaming a table and a foreign key fails using ef6

I use EF6 and code first migrations. I have a two tables

public class Team {

    public int Id { get; set; }

    public string Name { get; set; }
}

public class TeamMember {

    public int Id { get; set; }

    public string Name { get; set; }

    public Team Team { get; set; }

    public int TeamId { get; set; }

}

I want to rename TeamMember to TeamMemberDeprecated and add a new tabled named TeamMember with some differences to the table layout. The main reason I am creating a new table is data. I want to save all of the data in the current TeamMember table so I need to rename it, and I want to transfer only the specific data I need to the new TeamMember table.

When I add the migration it looks like this

RenameTable(name: "dbo.TeamMember", newName: "TeamMemberDeprecated");

What it doesn't do is change the name of the foreign key property from FK_dbo.TeamMember_dbo.Team_TeamId to FK_dbo.TeamMemberDeprecated_dbo.Team_TeamId.

This presents a problem when I go to create the new TeamMember table because the foreign key FK_dbo.TeamMember_dbo.Team_TeamId already exists in the database.

I tried dropping the foreign key and renaming it, but this doesn't do anything and when I run the migration -verbose I see why it's expecting the foreign key to be null. I want to keep the data, but perhaps my approach is wrong.

Any suggestions are welcome, thanks.

Upvotes: 0

Views: 391

Answers (2)

Red
Red

Reputation: 3267

In EF Core 5.?? there should be a few new commands (see EFCore GitHub Pull):

  • RenamePrimaryKey
  • RenameUniqueConstraint
  • RenameForeignKey

However, it seems that these will drop and recreate the key/constraint. In MSSQL this is not only unnecessary, but very costly. When the constraint is reapplied the database much check that each item is unique or present in the foreign table. If you can help it, do not do this.

Instead I recommend the below. You must specify the schema in the first parameter for the new name, but not in the second. The square brackets will help if any lunatic has put dots in table or constraint names:

migrationBuilder.Sql("sp_rename '[dbo].[PK_TeamMemberDeprecated]', '[PK_TeamMember]'");

It works the same for foreign keys:

migrationBuilder.Sql("sp_rename '[dbo].[FK_TeamMemberDeprecated_TeamID]', '[FK_TeamMember_TeamID]'");

Upvotes: 0

The One
The One

Reputation: 4706

Let EF rename the table, then go to SQL management studio and manually rename the foreign keys.

Go back to EF, make the changes, run the migration and let EF create the new foreign keys

Don't forget to backup your DB

Upvotes: 1

Related Questions