0

I'm trying to create a database for a school project and have to use multiple foreign keys. I already added raw data, and I'm wondering how to populate the foreign key columns dynamically.

For example I've got a city table and a country table. The city table has a country_id column that will contain the id from the right entry in the country table.

For now, the table city has the following columns :

id,

name,

code (like 'USA' for the US and the country has the same info, I was thinking about using that as an identifier with a WHERE statement)

country_id

I'm a complete beginner with mySql and feel totally lost right now !

Thanks for the help

Marion
  • 3
  • 1
  • If city.code is identical to a field in country, then you could certainly use that to grab the country.id. But what usually happens when you try to normalize a database *after* the data is present is you’ll find some edge cases (ie us instead of USA). Ideally, the normalization should be done when the records are inserted. – Tim Morton Apr 27 '19 at 20:21
  • Thank you for your answer. Yes, I want to use the code as an identifier to retrieve the right country.id The problem is, I have no idea -syntax wise- how to write an sql operation that allows me to loop over all cities, get the country code compare it with the other table, then insert the id ! Any idea how that can be written ? – Marion Apr 28 '19 at 12:58
  • Could you show a few rows of sample data for your tables (in the post). I need to know where you are in able to point you in the right direction – Tim Morton Apr 28 '19 at 16:49
  • Sure ! the table city looks like this : columns : id, name, code, country_id (to be filled) 1 Amsterdam NED || 2 Anvers BEL || 3 Athènes GRE || 4 Atlanta USA || 5 Barcelone ESP || the country table : columns : id, name, code 1 Afghanistan AFG || 2 Albanie ALB || 3 Algérie ALG || 4 Andorre AND || 5 Angola ANG || – Marion Apr 28 '19 at 17:03

2 Answers2

1

Before normalization, a flat file could be used to describe the city and state data:

id city       code country
 1 Amsterdam  NED  Netherlands
 2 Anvers     BEL  Belgium
 3 Athènes    GRE  Greece
 4 Atlanta    USA  United States
 5 Barcelone  ESP  Spain

For this data, no more normalization is necessary. In fact, it would be counter-productive.

But what if you wanted to store information about the country that you don't want repeated in every city's record? i.e., you wanted to keep population size, crime rate, name of currency... whatever. You wouldn't want to store that information with every record that references the country; that would be a lot of duplicated information, and if you wanted to update the country's data, you'd have to change it in every record that has a city in that country.

This is where the concept of foreign keys comes in to play. You would split this into two different tables: city and country. Information that is specific about a city (city name etc) goes into the city table, and information that is specific to a country (population of country, currency name, etc) goes in to the country table.

But now, how do we populate the tables?

In a web-based environment, typically you would have a data entry page that would have an input for the city name, and a drop down box (or AJAX lookup) for the country with the value being the id for the country name. This way, when you submit the city and country pair, you have the country id to insert with the name of the city.

But in this case, you already have the city and country tables populated. In order to update the city table with the country id, you have to be able to join the tables. Fortunately, you have such a field in the country code. This is a happy accident, because this is actually duplicate information that shouldn't be in the city table... not to mention it's really not a good idea to rely on a varchar field to join on (hard to guarantee that values are identical)

But since it's there, let's use it!

First, how do you connect the tables? You join them on a field that they share.

Given:

City:
id  name      code  country_id 
 1 Amsterdam  NED 
 2 Anvers     BEL 
 3 Athènes    GRE 
 4 Atlanta    USA 
 5 Barcelone  ESP 


 Country
 id  name       code 
  1 Afghanistan AFG 
  2 Albanie     ALB 
  3 Algérie     ALG 
  4 Andorre     AND 
  5 Angola      ANG 
  ...
  20 Belgium    BEL
  ...
  30 Netherlands NED

SELECT CITY.*, COUNTRY.*
FROM CITY
INNER JOIN COUNTRY ON CITY.CODE = COUNTRY.CODE

result (only two match with the result set I'm showing):

 1 Amsterdam  NED null 30 Netherlands NED
 2 Anvers     BEL null 20 Belgium     BEL

Now that you can join the two tables, you can update the city table based on that data. MySql has its own way to do that:

UPDATE CITY
INNER JOIN COUNTRY ON CITY.CODE=COUNTRY.CODE
SET CITY.COUNTRY_ID = COUNTRY.ID

(See MySQL - UPDATE query based on SELECT Query)

Tim Morton
  • 2,614
  • 1
  • 15
  • 23
  • Nice clarification for the newbie, especially when someone indicates they are new. so many times, I just see here's a query without helping explain WHY. Sometimes clarifying an explanation with the OP's context of data is the best source :) – DRapp Apr 29 '19 at 02:16
  • Hey ! Wow thank you so much, you made it very clear. I just tried that, and it worked like a charm ! Thank you for taking the time to give me such a detailed answer ! – Marion Apr 29 '19 at 13:08
0

You can just select all city from city table and take the code and compare to that of to the country table, when match found insert the country_id in city table. I hope it will help you.

Kirsten Phukon
  • 314
  • 3
  • 17
  • Thanks for your answer ! Yep that's exactly what I'm thinking. But how do you loop over all the entries of a table, compare data with another table, then insert the id ? I don't know what mysql syntax could do that kind of operation. – Marion Apr 28 '19 at 12:55
  • I hope u got your answer given by Tim Morton. – Kirsten Phukon May 06 '19 at 02:44