PostgreSQL-外键引用互斥表 [英] PostgreSQL - Foreign Key References Mutually Exclusive Tables

问题描述

我有三个数据库表：ALIENS，MONSTERS和TROPHIES。

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

每个ALIEN可以拥有多个TROPHIES。
每个怪物可以拥有多个奖杯。
每个TROPHY必须恰好有一个WINNER（ALIEN XOR MONSTER）。

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

在TROPHY表中是否有一种引用主键的外键？

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?

还是简单地拥有两个表就更容易了：ALIEN_TROPHY表和MONSTER_TROPHY表（即使它们是相同的）？ / p>

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

推荐答案

您可以创建两个带有检查约束的外键，该约束说一个正好为空：

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

这篇关于PostgreSQL-外键引用互斥表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！