Referencing column 'column' and referenced column 'Id' in foreign key constraint 'foreign_key' are incompatible

Question

I am trying to generate a migration using entity framework but I keep bumping into

Referencing column 'TaskId' and referenced column 'Id' in foreign key constraint 'FK_WorkerTaskTargetReference_WorkerTask_TaskId' are incompatible.

And I have no idea why.

My configuration seems to be correct:

public class WorkerTaskConfiguration : EntityConfiguration<WorkerTask>
    {
        public override void Configure()
        {
            Entity.Property(x => x.CreatedBy).IsRequired().HasMaxLength(DataModelConstants.DEFAULT_FIELD_LENGTH);
            Entity.Property(x => x.UpdatedBy).IsRequired().HasMaxLength(DataModelConstants.DEFAULT_FIELD_LENGTH);
            Entity.HasMany(x => x.Messages).WithOne(x => x.Task).HasForeignKey(x => x.TaskId).IsRequired();
            Entity.HasMany(x => x.TargetReferences).WithOne(x => x.Task).HasForeignKey(x => x.TaskId).IsRequired();
        }
}

  public class WorkerTaskTargetReferenceConfiguration : EntityConfiguration<WorkerTaskTargetReference>
    {
        public override void Configure()
        {
            Entity.HasKey(e => e.Id);
            Entity.Property(e => e.CreatedBy).IsRequired().HasMaxLength(DataModelConstants.DEFAULT_FIELD_LENGTH);
            Entity.Property(e => e.UpdatedBy).IsRequired().HasMaxLength(DataModelConstants.DEFAULT_FIELD_LENGTH);
        }
    }

But when I am applying my migration:

Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `WorkerTaskTargetReference` (
    `Id` char(36) NOT NULL,
    `Type` int NOT NULL,
    `ReferencedId` char(36) NOT NULL,
    `CreatedOn` datetime(6) NOT NULL,
    `UpdatedOn` datetime(6) NOT NULL,
    `CreatedBy` varchar(512) CHARACTER SET utf8mb4 NOT NULL,
    `UpdatedBy` varchar(512) CHARACTER SET utf8mb4 NOT NULL,
    `TaskId` char(36) NOT NULL,
    CONSTRAINT `PK_WorkerTaskTargetReference` PRIMARY KEY (`Id`),
    CONSTRAINT `FK_WorkerTaskTargetReference_WorkerTask_TaskId` FOREIGN KEY (`TaskId`) REFERENCES `WorkerTask` (`Id`) ON DELETE CASCADE
);
MySql.Data.MySqlClient.MySqlException (0x80004005): Referencing column 'TaskId' and referenced column 'Id' in foreign key constraint 'FK_WorkerTaskTargetReference_WorkerTask_TaskId' are incompatible.

Migration:

    migrationBuilder.CreateTable(
        name: "WorkerTaskTargetReference",
        columns: table => new
    {
        Id = table.Column<Guid>(nullable: false),
        Type = table.Column<int>(nullable: false),
        ReferencedId = table.Column<Guid>(nullable: false),
        CreatedOn = table.Column<DateTimeOffset>(nullable: false),
        UpdatedOn = table.Column<DateTimeOffset>(nullable: false),
        CreatedBy = table.Column<string>(maxLength: 512, nullable: false),
        UpdatedBy = table.Column<string>(maxLength: 512, nullable: false),
        TaskId = table.Column<Guid>(nullable: false)
    },
        constraints: table =>
        {
            table.PrimaryKey("PK_WorkerTaskTargetReference", x => x.Id);
            table.ForeignKey(
                name: "FK_WorkerTaskTargetReference_WorkerTask_TaskId",
                column: x => x.TaskId,
                principalTable: "WorkerTask",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
        });

    migrationBuilder.CreateIndex(
        name: "IX_WorkerTaskTargetReference_TaskId",
        table: "WorkerTaskTargetReference",
        column: "TaskId");

And I do not understand what I am doing wrong.

What datatype is `WorkerTask.Id` I expect its not a char(36) — RiggsFolly, Dec 17 '20 at 12:59
It is the sql statement that counts. Pls check WorkerTask.Id field's data type in mysql. — Shadow, Dec 17 '20 at 13:08

score 12 · Accepted Answer · answered Dec 17 '20 at 13:26

I have found out the solution.

My tables where with collation: utf8mb4_unicode_ci, while my database schema was with utf8mb4_0900_ai_ci

I changed my default server schema to utf8mb4_unicode_ci and it did the trick

Since they were chars, they are different because of schema

score 8 · Answer 2 · answered Jul 01 '21 at 21:38

8

The above answer did not solve it for me.
In my case the referencing column data type was int, and that differed from the referenced column that was bigint in the foreign key constraint. By making the data types the same solved it for me.

answered Jul 01 '21 at 21:38

Hmerman6006

1,622
1
20
45

Same was my case. Thanks – Rameez Raja Sep 07 '21 at 10:36
can you add the way you specified the data type in your migration ? (adding :type => :bigint to the add_reference function ) – Flooo49 Jun 30 '22 at 12:30
The question is tagged Mysql, so I had this problem in pure sql not entity-framework. But if you need an answer try: https://stackoverflow.com/questions/32266917/use-int-id-for-a-bigint-column-in-ef6. – Hmerman6006 Jun 30 '22 at 13:44
for django i go to phpmyadmin and change ID filed to bigint. This problem for django > v.4, because they include bigint for all ID field and if you updated django, you have this problem. sorry for my eng – JopaBoga Dec 08 '22 at 19:51
@JopaBoga Laravel also started doing the same with versions greater than 7 I believe. – Hmerman6006 Dec 09 '22 at 06:34
In my case, the Attribute is also different. – kunj kanani Feb 22 '23 at 06:20

score 0 · Answer 3 · answered May 30 '23 at 08:57

Commenting here for those whom the above answer might not helped them.
In my case it was different types of the attribute.
The referenced attribute was:
storeId: { type: DataTypes.INTEGER({ unsigned: true }) }

While the referencing attribute was:
storeId: { type: DataTypes.INTEGER }

Referencing column 'column' and referenced column 'Id' in foreign key constraint 'foreign_key' are incompatible

3 Answers3