CODEWITHSUNDEEP
postgresql
Osha Wott
Osha Wott

Reputation: 61

PostgreSQL - Foreign Key References Mutually Exclusive Tables

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)?

Upvotes: 6

Views: 971

Answers (1)

Andomar
Andomar

Reputation: 238296

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

Upvotes: 2

Related Questions