时间戳

首页 > 解决方案 > MySqlException：自引用表上的“外键约束格式错误”

mysql - MySqlException：自引用表上的“外键约束格式错误”

问题描述

我想创建一个菜单项表，其中每个项目都有一个父项和一组子项。EF Core 3.1 处理迁移正常，但是更新数据库时，MySql 抛出错误：

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

菜单项实体：

    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

解决方案

删除数据库并再次运行dotnet ef database update解决了问题，但这不是生产的解决方案。

推荐阅读