Let's imagine I have 2 tables:
Regions(RegionID, RegionName, RegionRank) whereRegionRankcan either bePrimaryorSecondaryCountry(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.
Country.Primary_RegionID = fk_Primary_RegionIDthat refers toRegion(RegionID, RegionRank = "Primary")Country.Secondary_RegionID = fk_Primary_RegionIDthat refers toRegion(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