sql - 由具有特定值的 2 列组成的外键

Question

假设我有 2 个表:

• Regions(RegionID、RegionName、RegionRank)，其中 RegionRank 可以是 Primary 或 Secondary

• 国家(CountryID、CountryName、Primary_RegionID、Secondary_RegionID)

区域表可能如下所示:

RegionID  RegionName      RegionRank
1         Nordic          Primary
2         NordEuropean    Secondary
3         GermanSpeaking  Primary
4         Iberia          Primary
5         SouthEuropean   Secondary

国家表可能如下所示:

CountryID  CountryName   Primary_RegionID    Secondary_RegionID
1          Sweden        1 (Nordic)          2 (NordEuropean)
2          Germany       3 (GermanSpeaking)  2 (NordEuropean)
3          Spain         4 (Iberia)          5 (SouthEuropean)

我想为 Country 创建 2 个外键。

1. Country.Primary_RegionID = fk_Primary_RegionID 指的是 Region(RegionID, RegionRank = "Primary")

2. Country.Secondary_RegionID = fk_Primary_RegionID 指的是 Region(RegionID, RegionRank = "Secondary")

我该怎么做？如何创建具有此类检查约束的 fk？我可以创建 2 个不同的表 (Region_Primary) 和 (Region_Secondary)，但我认为这不是一个优雅的解决方案。也许它更容易。

谢谢

Answer 1

这有点棘手，但您可以使用计算列和额外的唯一键来完成。

alter table regions
    add constraint unique unq_regions_primary on (RegionRank, RegionName);

alter table countries add PrimaryRank as ('Primary');
alter table countries add SecondaryRank as ('Secondary');

alter table countries
    add constraint fk_countries_primary foreign key (PrimaryRank, Primary_RegionID) references regions (RegionRank, RegionId);

alter table countries
    add constraint fk_countries_secondary foreign key (SecondaryRank, Secondary_RegionID) references regions (RegionRank, RegionId);