多个FK关系中只有1个约束 [英] Only 1 of Multiple FK relationships Constraint

问题描述

我有一个逻辑表结构，其中 Table A 与多个其他表( Table B & Table C )有关系.但是在功能上表A只能填充 B 或 C`的FK.

I have a logical table structure where Table A has relationship to multiple other tables (Table B & Table C). But functionally Table A can only ever have the FK forBorC` populated.

有效的表A 记录:

|------|--------|---------|---------|
| ID   | Name   | FK_B    | FK_C    |
|------|--------|---------|---------|
| 1    | Record | -null-  | 3       |
|------|--------|---------|---------|

无效的表A 记录:

|------|--------|---------|---------|
| ID   | Name   | FK_B    | FK_C    |
|------|--------|---------|---------|
| 1    | Record | 16      | 3       |
|------|--------|---------|---------|

我想知道如何正确地定义此约束，因为与数据库接口的API代码不是唯一的门.

I am wondering how to properly define this constraint properly as so the API code interfacing with the DB is not the only gate on this.

到目前为止，我有以下内容，但它不限制关系的单个记录-null-要求，并且允许上面的两个示例.

So far I have the following, but it does not restrict the single record -null- requirement of the relationship and allows both examples above.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<TableA>()
       .HasIndex(p => new { p.FK_B, p.FK_C });
}

我如何定义此约束以要求仅填充这些FK列之一，而其他则为null?

How do I define this constraint to require that only one of these FK columns are populated and the others are null?

该数据库的所有代码都是使用属性和Fluent API的EF核心代码优先.原始SQL解决方案虽然可以接受，但在此项目中将变得更加难以管理.因此，我正在寻找适合这种限制的解决方案.

All the code for the DB is EF Core Code First using Attributes and the Fluent APIs. Raw SQL solutions, while acceptable, are going to be harder to manage in this project. So I am looking for a solution that fits into this restraint.

推荐答案

花了一天的时间研究这个问题，并通过提出的答案和评论，我找到了所需的答案.似乎最新版本的EFCore可以解决问题！

After spending a day researching this and through the proposed answers and comments I have found the answer I was looking for. Seems that the latest version of EFCore answers the question!

需要EFCore 3.0

在EFCore 3.0中，引入了 HasCHeckConstraint ，以提供代码优先"解决方案来生成检查约束".以下示例说明了SQL检查语法接口.有一种语法带有bool标志来将DataAnnotations解释为约束，但是此处的文档受到限制.文档

In EFCore 3.0 HasCHeckConstraint was introduced to provide a Code First solution to generating 'Check Constraints'. The following example illustrates the SQL Check Syntax Interface(s). There is a syntax that takes a bool flag to interpret DataAnnotations as constraints but the documentation is limited here. Documentation

与必须管理SQL脚本或手动迁移(如下)相比，此解决方案要好得多.尽管它仍然依赖于原始SQL语句.最终，如果为此的Fluent API获得了CodeGen方法版本，以便您可以提供C#函数而不是SQL，则可以在更改约束中的字段名称时更好地进行处理.

This solution is much better than having to manage a SQL script or a Manual Migration (below). Though it still relies on a raw SQL statement. Ultimately, if the Fluent API for this gets a CodeGen method version so that you can provide a C# function instead of SQL that would provide for better handling when a field name in the constrain is changed.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<TableA>()
       .HasCheckConstraint("CHK_OnlyOneForeignKey", "
           ( CASE WHEN FK_B IS NULL THEN 0 ELSE 1 END
           + CASE WHEN FK_C IS NULL THEN 0 ELSE 1 END
           ) = 1
    ");
}

EFCore迁移编辑

在所有版本的EFCore中，您可以编辑生成的迁移文件，并利用 MigrationBuilder 上的 Sql(...)方法来应用任何SQL命令.

EFCore Migration editing

In all versions of EFCore you can edit a generated Migration File and utilize the Sql(...) method on MigrationBuilder to apply any SQL commands.

注意:通常为此目的创建一个空迁移.

Note: It is common to create an empty Migration for this purpose.

public partial class CustomCheckConstraint : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("<SQL to alter target table and add a constraint>");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Code to remove Constraint added in Up method
    }
}

感谢@fenixil和@Ian Mercer向我指出有助于我完善研究的解决方案.

Thank you @fenixil and @Ian Mercer for pointing me towards solutions that helped me refine my research.

这篇关于多个FK关系中只有1个约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！