How can I revert to an older migration in EF Core? (FK key constraint)

520 Views Asked by At

I am having problems reverting the database model to an earlier migration in EF core. (Version 2.1.14)

I am attempting to update the database model to a previous Migration before I deleted the "WorkingHour" table because I have re-deployed an older version of my build. In other words, I expect the Working hours table to be created upon rollback because that's in the definiton of the "Down" method in the Migration that dropped the table.

I use the command update-database [migration-prior-to-deleting-workinghours-tale] in an attempt to revert to a previous state of the database.

I get an SqlException

Column Users.UserId is not the same length or scale as referencing column "WorkingHours.AdminUserId" in foregin key "FK_WorkingHours_Users_AdminUserId" Columns participating in a foreign key relationship must be defined with the same length and scale.

Which I find strange because that state of the database has been valid in the past. And I would therefore not expect to make any changes to my code in order to roll back to an older (working) build.

I have a model User which has a collection of model WorkingHours like this:

public class User 
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)] //Manually assigned
    public string UserId {get;set;}
    public ICollection<WorkingHour> WorkingHours {get;set;}
}

The WorkingHour model:

public class WorkingHour
{
    public int Id {get;set;}
    public User Admin {get;set;}
}

I have applied a migration that did this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(name: "WorkingHours");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
            name: "WorkingHours",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                AdminUserId = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_WorkingHours", x => x.Id);
                table.ForeignKey(
                    name: "FK_WorkingHours_Users_AdminUserId",
                    column: x => x.AdminUserId,
                    principalTable: "Users",
                    principalColumn: "UserId",
                    onDelete: ReferentialAction.Restrict);
            });

        migrationBuilder.CreateIndex(
            name: "IX_WorkingHours_AdminUserId",
            table: "WorkingHours",
            column: "AdminUserId");
}

It is when I revert this migration and attempting to re-create the table WorkingHours I get the following error:

System.Data.SqlClient.SqlException (0x80131904): Column 'Users.UserId' is not the same length or scale as referencing column 'WorkingHours.AdminUserId' in foreign key 'FK_WorkingHours_Users_AdminUserId'. Columns participating in a foreign key relationship must be defined with the same length and scale.

Any ideas of what I have done wrong?

0

There are 0 best solutions below