EF Core迁移报错：违反TblScheme表外键约束FK_TblScheme_TblCompanies_CompanyId

问题场景 #

为现有表创建新关联关系时，执行EF Core数据库迁移反复出现外键约束冲突错误，错误日志如下：

Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE "TblScheme" ADD CONSTRAINT "FK_TblScheme_TblCompanies_CompanyId" FOREIGN KEY ("CompanyId") REFERENCES "TblCompanies" ("paramID") ON DELETE CASCADE;
Failed executing DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "TblScheme" ADD CONSTRAINT "FK_TblScheme_TblCompanies_CompanyId" FOREIGN KEY ("CompanyId") REFERENCES "TblCompanies" ("paramID") ON DELETE CASCADE;
Npgsql.PostgresException (0x80004005): 23503: insert or update on table "TblScheme" violates foreign key constraint "FK_TblScheme_TblCompanies_CompanyId"

DETAIL: Key (CompanyId)=(0) is not present in table "TblCompanies".
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   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 connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 23503
    MessageText: insert or update on table "TblScheme" violates foreign key constraint "FK_TblScheme_TblCompanies_CompanyId"
    Detail: Key (CompanyId)=(0) is not present in table "TblCompanies".
    SchemaName: public
    TableName: TblScheme
    ConstraintName: FK_TblScheme_TblCompanies_CompanyId
    File: ri_triggers.c
    Line: 2539
    Routine: ri_ReportViolation
23503: insert or update on table "TblScheme" violates foreign key constraint "FK_TblScheme_TblCompanies_CompanyId"

DETAIL: Key (CompanyId)=(0) is not present in table "TblCompanies".

此前通过删除重建表临时解决，但效率低下，需更合理的处理方式。

问题原因 #

PostgreSQL在添加外键约束时会校验所有现有数据的合法性，当前TblScheme表中存在CompanyId=0的记录，但TblCompanies表中没有paramID=0的对应数据，导致约束校验失败。

解决方案 #

1. 先修复现有数据（推荐）

这是最彻底的解决方式，确保数据符合约束规则：

• 首先查询所有不符合约束的记录：

  SELECT * FROM "TblScheme" WHERE "CompanyId" NOT IN (SELECT "paramID" FROM "TblCompanies");
  

• 根据业务需求处理这些记录：
  • 若为无效数据，直接删除：

    DELETE FROM "TblScheme" WHERE "CompanyId" NOT IN (SELECT "paramID" FROM "TblCompanies");
    

  • 若需保留数据，先在TblCompanies中补充对应的paramID=0记录，或把TblScheme中的CompanyId更新为TblCompanies中已存在的合法值。

完成数据修复后，重新执行迁移即可成功添加外键约束。

2. 调整迁移脚本（适合无法立即修复数据的场景）

如果暂时不能修改现有数据，可以手动调整EF Core生成的迁移文件，先添加不校验历史数据的约束，后续再清理数据：

1. 打开自动生成的迁移类，找到添加外键的代码，替换为PostgreSQL支持的NOT VALID语法：

   // 替换原迁移代码为手动执行SQL
   migrationBuilder.Sql(@"ALTER TABLE ""TblScheme"" ADD CONSTRAINT ""FK_TblScheme_TblCompanies_CompanyId"" FOREIGN KEY (""CompanyId"") REFERENCES ""TblCompanies"" (""paramID"") ON DELETE CASCADE NOT VALID;");
   

2. 可选：添加后续验证约束的SQL（可异步执行，不阻塞业务）：

   migrationBuilder.Sql(@"ALTER TABLE ""TblScheme"" VALIDATE CONSTRAINT ""FK_TblScheme_TblCompanies_CompanyId"";");
   

   注意：使用NOT VALID后，新插入/更新的数据会被约束，但历史非法数据仍存在，需尽快清理避免业务逻辑异常。

3. 预防后续问题

• 在实体类中为CompanyId设置合理的默认值，避免默认0这类无意义的外键值；
• 若外键为必填项，在实体类标记[Required]，从应用层避免插入无效值；
• 执行迁移前先检查数据一致性，提前排查潜在的约束冲突。

内容的提问来源于stack exchange，提问作者Daniel