Foreign key on IdentityUser cannot be set ef-code-first entity-framework-core mysql


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
  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)
  CHARSET = latin1;

  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?

9/11/2018 5:38:37 PM

Accepted Answer

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.

9/13/2018 3:00:29 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow