Woot Woot - 3 years ago 89
SQL Question

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.

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download