Foreign key on IdentityUser cannot be set

Question

I'm trying to create a new model with a foreign key to an IdentityUser using .NET Core 2.1 and Entity Framework Core (backed by MySQL) using the code-first approach. I've created a model that looks like this:

using System;
using Microsoft.AspNetCore.Identity;

namespace Models
{
    public class Note
    {
        public int NoteId { get; set; }

        public string NoteText { get; set; }

        public string CreatedByUserId { get; set; }
        public virtual IdentityUser CreatedByUser { get; set; }
    }
}

Generating the migration looks good, but when I actually try to run the database update, I get a "Cannot add foreign key contstraint" error.

Here's the SQL statement it's failing on:

ALTER TABLE `Notes` ADD CONSTRAINT `FK_Notes_AspNetUsers_CreatedByUserId` FOREIGN KEY (`CreatedByUserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE NO ACTION;

That results in a slightly more detailed error message: "There is no index in the referenced table where the referenced columns appear as the first columns."

However, when I look at the indexes on the AspNetUsers table, I can see that Id is the primary key, which should be indexed. Here's the create statement for the table:

CREATE TABLE aspnetusers
(
  Id                   VARCHAR(127) NOT NULL
    PRIMARY KEY,
  AccessFailedCount    INT          NOT NULL,
  ConcurrencyStamp     LONGTEXT     NULL,
  Email                VARCHAR(256) NULL,
  EmailConfirmed       BIT          NOT NULL,
  LockoutEnabled       BIT          NOT NULL,
  LockoutEnd           DATETIME(6)  NULL,
  NormalizedEmail      VARCHAR(256) NULL,
  NormalizedUserName   VARCHAR(256) NULL,
  PasswordHash         LONGTEXT     NULL,
  PhoneNumber          LONGTEXT     NULL,
  PhoneNumberConfirmed BIT          NOT NULL,
  SecurityStamp        LONGTEXT     NULL,
  TwoFactorEnabled     BIT          NOT NULL,
  UserName             VARCHAR(256) NULL,
  CONSTRAINT UserNameIndex
  UNIQUE (NormalizedUserName)
)
  ENGINE = InnoDB
  CHARSET = latin1;

CREATE INDEX EmailIndex
  ON aspnetusers (NormalizedEmail);

I have also tried using NormalizedUserName and NormalizedEmail in place of Id since they both appear to be indexed fields that show up as the only field in their respective indexes, but I get the same error message for all of them.

How do I go about setting up a foreign key to an IdentityUser table?

Answer 1

It turns out the problem for me was that the two tables that referenced each other weren't using the same character set, so MySQL was refusing to set up the foreign key constraint. I think it may be a case where I imported the initial db from a mysqldump, but the defaults on my local db didn't match what was output to the sql dump.