Search code examples
sqlpostgresqlelixirecto

Ecto - Foreign key constraint on table A for table B or C, but not both B & C

Let's say that table A can belong to either table B or table C, but not both.

It seems that it is possible to use foreign key constraints for this scenario in SQL Server.

How could this relationship be represented in Elixir's Ecto library?

Solution

  • You can structure this as using a separate column for each foreign key constraint and then guaranteeing that only one is populated:

    create table A (
    . . . 
    b_id int,
    c_id int,
    foreign key (b_id) references b(b_id),
    foreign key (c_id) references c(c_id),
    check (b_id is null or c_id is null)
)