Migrations scaffolding error when renaming table and adding another table with FK to first table #6340
Description
Steps to reproduce
- Create a migration for a data model with entity A and update-database.
- Change the data model to (a) change the table name for entity A and (b) add table B with a foreign key to table A.
- Scaffold a second migration and run update-database.
The result is an error like this:
"There are no primary or candidate keys in the referenced table 'Courses' that match the referencing column list in the foreign key 'FK_CourseInstructor_Course_CourseID'.
Could not create constraint or index."
The issue
The Up method does things in this order:
- Drops primary key of table A
- Creates Table B with FK constraints
- Adds back primary key of table A with new PK name reflecting table name change
- Renames table A
The problem is that in step 2 when trying to create the FK constraint from Table B to table A, table A has no primary key, so the FK creation fails.
Further technical details
EF Core version: 1.0.0
Operating system: Win 10
Visual Studio version: VS 2015
Here is the code generated by migrations in my project, with the create table code causing the problem commented out and moved to a later location where it works because the PK still exists. There were three tables before the migration, one of which was "Courses". The migration renamed Courses to Course and added CourseInstructor, a many-to-many join table.
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropForeignKey(
name: "FK_Enrollments_Courses_CourseID",
table: "Enrollments");
migrationBuilder.DropForeignKey(
name: "FK_Enrollments_Students_StudentID",
table: "Enrollments");
migrationBuilder.DropPrimaryKey(
name: "PK_Students",
table: "Students");
migrationBuilder.DropPrimaryKey(
name: "PK_Enrollments",
table: "Enrollments");
migrationBuilder.DropPrimaryKey(
name: "PK_Courses",
table: "Courses");
migrationBuilder.CreateTable(
name: "Instructors",
columns: table => new
{
ID = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
FirstName = table.Column<string>(maxLength: 50, nullable: false),
HireDate = table.Column<DateTime>(nullable: false),
LastName = table.Column<string>(maxLength: 50, nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Instructors", x => x.ID);
});
// This is where the code that creates the new table was generated.
//migrationBuilder.CreateTable(
// name: "CourseInstructor",
// columns: table => new
// {
// CourseID = table.Column<int>(nullable: false),
// InstructorID = table.Column<int>(nullable: false)
// },
// constraints: table =>
// {
// table.PrimaryKey("PK_CourseInstructor", x => new { x.CourseID, x.InstructorID });
// table.ForeignKey(
// name: "FK_CourseInstructor_Course_CourseID",
// column: x => x.CourseID,
// principalTable: "Courses",
// principalColumn: "CourseID",
// onDelete: ReferentialAction.Cascade);
// table.ForeignKey(
// name: "FK_CourseInstructor_Instructors_InstructorID",
// column: x => x.InstructorID,
// principalTable: "Instructors",
// principalColumn: "ID",
// onDelete: ReferentialAction.Cascade);
// });
migrationBuilder.CreateTable(
name: "Department",
columns: table => new
{
DepartmentID = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
Budget = table.Column<decimal>(type: "money", nullable: false),
InstructorID = table.Column<int>(nullable: true),
Name = table.Column<string>(maxLength: 50, nullable: true),
StartDate = table.Column<DateTime>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Department", x => x.DepartmentID);
table.ForeignKey(
name: "FK_Department_Instructors_InstructorID",
column: x => x.InstructorID,
principalTable: "Instructors",
principalColumn: "ID",
onDelete: ReferentialAction.Restrict);
});
migrationBuilder.CreateTable(
name: "OfficeAssignment",
columns: table => new
{
InstructorID = table.Column<int>(nullable: false),
Location = table.Column<string>(maxLength: 50, nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_OfficeAssignment", x => x.InstructorID);
table.ForeignKey(
name: "FK_OfficeAssignment_Instructors_InstructorID",
column: x => x.InstructorID,
principalTable: "Instructors",
principalColumn: "ID",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.AddColumn<int>(
name: "DepartmentID",
table: "Course",
nullable: false,
defaultValue: 0);
migrationBuilder.AddColumn<int>(
name: "InstructorID",
table: "Courses",
nullable: true);
migrationBuilder.AlterColumn<string>(
name: "LastName",
table: "Students",
maxLength: 50,
nullable: false);
migrationBuilder.AlterColumn<string>(
name: "FirstName",
table: "Students",
maxLength: 50,
nullable: false);
migrationBuilder.AddPrimaryKey(
name: "PK_Student",
table: "Students",
column: "ID");
migrationBuilder.AddPrimaryKey(
name: "PK_Enrollment",
table: "Enrollments",
column: "EnrollmentID");
migrationBuilder.AlterColumn<string>(
name: "Title",
table: "Courses",
maxLength: 50,
nullable: true);
migrationBuilder.AddPrimaryKey(
name: "PK_Course",
table: "Courses",
column: "CourseID");
migrationBuilder.CreateIndex(
name: "IX_Course_DepartmentID",
table: "Courses",
column: "DepartmentID");
migrationBuilder.CreateIndex(
name: "IX_Course_InstructorID",
table: "Courses",
column: "InstructorID");
//This is where I had to move the code, to get it to run
migrationBuilder.CreateTable(
name: "CourseInstructor",
columns: table => new
{
CourseID = table.Column<int>(nullable: false),
InstructorID = table.Column<int>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_CourseInstructor", x => new { x.CourseID, x.InstructorID });
table.ForeignKey(
name: "FK_CourseInstructor_Course_CourseID",
column: x => x.CourseID,
principalTable: "Courses",
principalColumn: "CourseID",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_CourseInstructor_Instructors_InstructorID",
column: x => x.InstructorID,
principalTable: "Instructors",
principalColumn: "ID",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_CourseInstructor_CourseID",
table: "CourseInstructor",
column: "CourseID");
migrationBuilder.CreateIndex(
name: "IX_CourseInstructor_InstructorID",
table: "CourseInstructor",
column: "InstructorID");
#endregion
migrationBuilder.CreateIndex(
name: "IX_Department_InstructorID",
table: "Department",
column: "InstructorID");
migrationBuilder.CreateIndex(
name: "IX_OfficeAssignment_InstructorID",
table: "OfficeAssignment",
column: "InstructorID",
unique: true);
migrationBuilder.AddForeignKey(
name: "FK_Course_Department_DepartmentID",
table: "Courses",
column: "DepartmentID",
principalTable: "Department",
principalColumn: "DepartmentID",
onDelete: ReferentialAction.Cascade);
migrationBuilder.AddForeignKey(
name: "FK_Course_Instructors_InstructorID",
table: "Courses",
column: "InstructorID",
principalTable: "Instructors",
principalColumn: "ID",
onDelete: ReferentialAction.Restrict);
migrationBuilder.AddForeignKey(
name: "FK_Enrollment_Course_CourseID",
table: "Enrollments",
column: "CourseID",
principalTable: "Courses",
principalColumn: "CourseID",
onDelete: ReferentialAction.Cascade);
migrationBuilder.AddForeignKey(
name: "FK_Enrollment_Student_StudentID",
table: "Enrollments",
column: "StudentID",
principalTable: "Students",
principalColumn: "ID",
onDelete: ReferentialAction.Cascade);
migrationBuilder.RenameIndex(
name: "IX_Enrollments_StudentID",
table: "Enrollments",
newName: "IX_Enrollment_StudentID");
migrationBuilder.RenameIndex(
name: "IX_Enrollments_CourseID",
table: "Enrollments",
newName: "IX_Enrollment_CourseID");
migrationBuilder.RenameTable(
name: "Students",
newName: "Student");
migrationBuilder.RenameTable(
name: "Enrollments",
newName: "Enrollment");
migrationBuilder.RenameTable(
name: "Courses",
newName: "Course");
}