c# - “dotnet ef 数据库更新”失败(外键约束失败)
问题描述
我想使用dotnet ef migrations add Seed
(成功)然后dotnet ef update database
(失败)为我的 Products 表添加一些初始数据。
dotnet ef update database
输出:
...
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (139ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `Products` (`Id`, `CategoryId`, `Name`, `QuantityInPackage`, `UnitOfMeasurement`)
VALUES (100, 100, 'Apple', 1, 1);
MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE) ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE)
.
.
.
Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE)
据我了解,它基本上是说我想将数据与不存在的不真实的东西相关联。然后我尝试通过复制粘贴错误消息中的查询直接通过mysql cli插入它,它起作用了。
为什么我无法通过 实现这一目标dotnet ef ...
?如何正确地做到这一点?
$ dotnet --version
2.2.202
$ dotnet ef --version
Entity Framework Core .NET Command-line Tools
2.2.3-servicing-35854
表“类别”
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(30) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
+-----+--------------------+
| Id | Name |
+-----+--------------------+
| 100 | Fruits and Veggies |
| 101 | Dairy |
| 102 | Bakery |
| 104 | Sweets |
+-----+--------------------+
表“产品”
+-------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| QuantityInPackage | smallint(6) | NO | | NULL | |
| UnitOfMeasurement | tinyint(3) unsigned | NO | | NULL | |
| CategoryId | int(11) | NO | MUL | NULL | |
+-------------------+---------------------+------+-----+---------+----------------+
empty
AppDbContext.cs
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<Category>().ToTable("Categories");
builder.Entity<Category>().HasKey(p => p.Id);
builder.Entity<Category>().Property(p => p.Id).IsRequired().ValueGeneratedOnAdd();
builder.Entity<Category>().Property(p => p.Name).IsRequired().HasMaxLength(30);
builder.Entity<Category>().HasMany(p => p.Products).WithOne(p => p.Category).HasForeignKey(p => p.CategoryId);
builder.Entity<Category>().HasData(
new Category { Id = 100, Name = "Fruits and Veggies" },
new Category { Id = 101, Name = "Dairy" }
);
builder.Entity<Product>().ToTable("Products");
builder.Entity<Product>().HasKey(p => p.Id);
builder.Entity<Product>().Property(p => p.Id).IsRequired().ValueGeneratedOnAdd();
builder.Entity<Product>().Property(p => p.Name).IsRequired().HasMaxLength(50);
builder.Entity<Product>().Property(p => p.QuantityInPackage).IsRequired();
builder.Entity<Product>().Property(p => p.UnitOfMeasurement).IsRequired();
builder.Entity<Product>().HasData(
new Product {
Id = 100,
Name = "Apple",
QuantityInPackage = 1,
UnitOfMeasurement = EUnitOfMeasurement.Unity,
CategoryId = 100
},
new Product {
Id = 101,
Name = "Emmental Cheese",
QuantityInPackage = 150,
UnitOfMeasurement = EUnitOfMeasurement.Gram,
CategoryId = 101
}
);
}
完整的dotnet ef update database
输出:
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.2.4-servicing-10062 initialized 'AppDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (68ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='SupermarketDB' AND TABLE_NAME='__EFMigrationsHistory';
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='SupermarketDB' AND TABLE_NAME='__EFMigrationsHistory';
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `MigrationId`, `ProductVersion`
FROM `__EFMigrationsHistory`
ORDER BY `MigrationId`;
info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20190604143215_AddSeeds'.
Applying migration '20190604143215_AddSeeds'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DELETE FROM `Categories`
WHERE `Id` = 100;
SELECT ROW_COUNT();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DELETE FROM `Categories`
WHERE `Id` = 101;
SELECT ROW_COUNT();
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (139ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `Products` (`Id`, `CategoryId`, `Name`, `QuantityInPackage`, `UnitOfMeasurement`)
VALUES (100, 100, 'Apple', 1, 1);
MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE) ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE)
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 43
at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 81
at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 307
at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 292
at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 37
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 276
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 77
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
Failed executing DbCommand (139ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `Products` (`Id`, `CategoryId`, `Name`, `QuantityInPackage`, `UnitOfMeasurement`)
VALUES (100, 100, 'Apple', 1, 1);
MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE) ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE)
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 43
at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 81
at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 307
at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 292
at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 37
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 276
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 77
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Cannot add or update a child row: a foreign key constraint fails (`SupermarketDB`.`Products`, CONSTRAINT `FK_Products_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`Id`) ON DELETE CASCADE)
解决方案
推荐阅读
- c++ - 切换到 Visual Studio 2019 后必须在此上下文中展开参数包
- safari - getAccessTokenAsync 仅在 safari 上返回 13001
- javascript - 将函数放入 javascript 对象中是一种好习惯吗?
- keras - 在 Google Colab TPU 上训练 CNN 的最佳方法
- reporting-services - How do I amend shared data source definitions after SSRS migration to a diff node?
- android - 如何知道有效的 FirebaseAuth 是否即将过期
- android - 注册以在通知被接受或拒绝时收听
- java - 间歇性“通信链路故障”
- c++ - 替代 while 语句
- django - 如何覆盖随响应返回的序列化程序对象 django rest framework 序列化程序