MySqlException: "Foreign key constraint is incorrectly formed" on self-referencing table

0

I want to create a menu items table where each item has a Parent and a collection of Children. EF Core 3.1 handles migration ok, but when updating database, MySql throws an error:

Failed executing DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `MenuItem` (
    `Id` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    `Created` datetime(6) NOT NULL,
    `Updated` datetime(6) NOT NULL,
    `Name` longtext CHARACTER SET utf8mb4 NULL,
    `Link` longtext CHARACTER SET utf8mb4 NULL,
    `IconId` varchar(255) CHARACTER SET utf8mb4 NULL,
    `ParentId` varchar(255) CHARACTER SET utf8mb4 NULL,
    `IsLong` tinyint(1) NOT NULL,
    `Type` int NOT NULL,
    `Published` tinyint(1) NOT NULL,
    `OrderIndex` int NOT NULL,
    CONSTRAINT `PK_MenuItem` PRIMARY KEY (`Id`),
    CONSTRAINT `FK_MenuItem_File_IconId` FOREIGN KEY (`IconId`) REFERENCES `File` (`Id`) ON DELETE RESTRICT,
    CONSTRAINT `FK_MenuItem_MenuItem_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `MenuItem` (`Id`) ON DELETE RESTRICT
);
MySql.Data.MySqlClient.MySqlException (0x80004005): Can't create table `x`.`MenuItem` (errno: 150 "Foreign key constraint is incorrectly formed")
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Can't create table `x`.`MenuItem` (errno: 150 "Foreign key constraint is incorrectly formed")
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49

MenuItemEntity:

    public class MenuItemEntity : Entity, IOrderedEntity, IPublishableEntity
    {
        public MenuItemEntity()
        {
            Children = new List<MenuItemEntity>();
        }
        public string Id { get; set; }
        public DateTime Created { get; set; }
        public DateTime Updated { get; set; }
        public string Name { get; set; }
        public string Link { get; set; }
        public FileEntity Icon { get; set; }
        
        [ForeignKey("ParentId")]
        public virtual MenuItemEntity Parent { get; set; }
        public virtual ICollection<MenuItemEntity> Children { get; set; }
        public bool IsLong { get; set; }

        public MenuItemsType Type { get; set; }
        public bool Published { get; set; }
        public int OrderIndex { get; set; }

        public override string ToString()
        {
            return Name;
        }
    }
mysql
asked on Stack Overflow Aug 3, 2020 by Dan Percic

1 Answer

0

Deleting database and running again dotnet ef database update solved problem, but it's not a solution for production.

answered on Stack Overflow Aug 4, 2020 by Dan Percic

User contributions licensed under CC BY-SA 3.0