Only 1 of Multiple FK relationships Constraint

Question

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.

Valid Table A Record:

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

Invalid Table A Record:

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

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.

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 });
}

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

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.

Answer 1

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!

Code First Solution

Requires EFCore 3.0

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

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 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
    }
}

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