PostgreSQL - Foreign Key References Reciprocal 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 refers to the primary key ALIEN or MONSTER?
Or is it easier to just have two tables: the ALIEN_TROPHY table and the MONSTER_TROPHY table (although they would be identical)?
+3
source to share
1 answer
You can create two foreign keys with a check constraint stating that one of them 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)
);
+1
source to share