After creating a model as such for MySQL using Entity Framework Core 3.1.7 I receive a error trying to run the migration; dotnet ef database update. The reason is that all the indexes are added at the end of the migration. Is it expected for me to go through and manually place the indexes after CREATE TABLE or is this a bug? Trying to consolidate all the migrations I have.
CREATE TABLE Children
(
CommonId
int NOT NULL,
ParentId
int NOT NULL,
ChildId
int NOT NULL,
Group_Roles
int NULL,
CONSTRAINT PK_Children
PRIMARY KEY (CommonId
, ParentId
, ChildId
),
CONSTRAINT FK_Children_Parents_CommonId_ParentId
FOREIGN KEY (CommonId
, ParentId
) REFERENCES Parents
(CommonId
, ParentId
) ON DELETE CASCADE
);
MySql.Data.MySqlClient.MySqlException (0x80004005): Failed to add the foreign key constraint. Missing index for constraint 'FK_Children_Parents_CommonId_ParentId' in the referenced table 'parents'
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Failed to add the foreign key constraint. Missing index for constraint 'FK_Children_Parents_CommonId_ParentId' in the referenced table 'parents'
public class Parent
{
public int CommonId { get; set; }
public int ParentId { get; set; }
public string Name { get; set; }
public virtual ICollection<Child> Children { get; } = new HashSet<Child>();
}
public class Child
{
public int CommonId { get; set; }
public int ParentId { get; set; }
public int ChildId { get; set; }
public virtual Parent Parent { get; set; }
}
public class TestContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseMySql("");
}
public DbSet<Parent> Parents { get; set; }
public DbSet<Child> Children { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Parent>(entity =>
{
entity.HasKey(e => new { e.CommonId, e.ParentId });
entity.Property(e => e.ParentId)
.ValueGeneratedOnAdd();
});
modelBuilder.Entity<Parent>().HasIndex(e => new { e.CommonId, e.ParentId }).IsUnique();
modelBuilder.Entity<Child>(entity =>
{
entity.HasKey(e => new { e.CommonId, e.ParentId, e.ChildId });
entity.HasOne(e => e.Parent)
.WithMany(e => e.Children)
.HasForeignKey(p => new { p.CommonId, p.ParentId });
});
}
}
Manual fix is to move modelBuilder.CreateIndex between the two tables?
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Parents",
columns: table => new
{
CommonId = table.Column<int>(nullable: false),
ParentId = table.Column<int>(nullable: false)
.Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
Name = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Parents", x => new { x.CommonId, x.ParentId });
});
migrationBuilder.CreateTable(
name: "Children",
columns: table => new
{
CommonId = table.Column<int>(nullable: false),
ParentId = table.Column<int>(nullable: false),
ChildId = table.Column<int>(nullable: false),
Group_Roles = table.Column<int>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Children", x => new { x.CommonId, x.ParentId, x.ChildId });
table.ForeignKey(
name: "FK_Children_Parents_CommonId_ParentId",
columns: x => new { x.CommonId, x.ParentId },
principalTable: "Parents",
principalColumns: new[] { "CommonId", "ParentId" },
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_Parents_CommonId_ParentId",
table: "Parents",
columns: new[] { "CommonId", "ParentId" },
unique: true);
}
User contributions licensed under CC BY-SA 3.0