Foreign key composed of 2 columns with specific values

Question

Let's imagine I have 2 tables:

• Regions (RegionID, RegionName, RegionRank) where RegionRank can either be Primary or Secondary

• Country (CountryID, CountryName, Primary_RegionID, Secondary_RegionID)

A region table could look like:

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

A country table could look like:

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

I would like to create 2 foreign key for Country.

1. Country.Primary_RegionID = fk_Primary_RegionID that refers to Region(RegionID, RegionRank = "Primary")

2. Country.Secondary_RegionID = fk_Primary_RegionID that refers to Region(RegionID, RegionRank = "Secondary")

How do I do that? How do I create a fk with such a check constraint? I could create 2 different tables (Region_Primary) and (Region_Secondary) but I don't find that an elegant solution. Perhaps it is easier.

Thanks

Answer 1

This is a bit tricky, but you can do it using computed columns and additional unique keys.

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