CODEWITHSUNDEEP
sqlsql-serverforeign-keys
mHelpMe
mHelpMe

Reputation: 6668

use foreign key constraint to ensure valid data entry

I have been cleaning up a database table which was bit of a nightmare.

I want to ensure that the data being entered going forward is correct. So I read about foreign key constraints.

So below is a table where the data gets entered into.

tblSales

  Country      Seller       Value
  52           01           100
  86           01           100
  102          32           100
  32           52           100
  52           01           100

I want to ensure the values being entered into the Country & Seller fields are a certain set of values. There is also a table called tblMap which is used for reports to give the numbers a name which is easy to read.

tblMap

Factor      Code      Name      
Country     52        US
Country     86        Germany
Country     102       Spain
Country     32        Italy
Seller      01        Bob
Seller      32        Sarah
Seller      52        Jim

So like I say I was going to use a foreign key constraint but I can't create a primary key on the Code field in tblMap as 52 is used for both a country and a seller. I am not able to change the code numbers either.

Am I still able to use a foreign key constrain to ensure any value entered into tblSales exists in tblMap?

Upvotes: 0

Views: 99

Answers (1)

aaDev
aaDev

Reputation: 143

May be you can replace tblMap by 2 tables tblMapCountry and tblMapSeller

tblMapCountry

Code      Name      
52        US
86        Germany
102       Spain
32        Italy

tblMapSeller

Code      Name      
01        Bob
32        Sarah
52        Jim

After you can base

  • a FK between tblSales.country and tblMapCountry
  • a FK between tblSales.seller and tblMapSeller

At the end you can build a view tblMap by union the 2 tables tblMapCountry and tblMapSeller

create view `tblMap`
as
select 'Country' as Factor,Code,Name from tblMapCountry
union all
select 'Seller' as Factor,Code,Name from tblMapSeller

Upvotes: 1

Related Questions