CODEWITHSUNDEEP
sqlsql-server-2008
Raul_Homburg
Raul_Homburg

Reputation: 31

Foreign key composed of 2 columns with specific values

Let's imagine I have 2 tables:

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

Upvotes: 3

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

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

Upvotes: 2

Related Questions