PostgreSQL - Foreign Key References Mutually Exclusive Tables

Question

I have three database tables: ALIENS, MONSTERS, and TROPHIES.

Each ALIEN can have multiple TROPHIES. Each MONSTER can have multiple TROPHIES. Each TROPHY must have exactly one WINNER (ALIEN XOR MONSTER).

Is there a way to have a foreign key in the TROPHY table that references the primary key of either an ALIEN or a MONSTER?

Or is it easier to simply have two tables: an ALIEN_TROPHY table and a MONSTER_TROPHY table (even though they would be identical)?

Answer 1

You could create two foreign keys with a check constraint that says exactly one is empty:

create table alien (id int primary key);
create table monster (id int primary key);
create table trophy (id int primary key,
    alien_id int references alien(id),
    monster_id int references monster(id),
    check (alien_id is null <> monster_id is null)
);