1

I have two csv files, countries and birth_rate_stat with the following data:

ISO ISO3 ISO_CODE FIPS Display Name Currency Name Phone_Code Region Code
AF AFG 4 AF Afghanistan Afghani 93 Asia
AX ALA 248 Aland Islands Euro 340 Europe
country_code country_name year birth_rate
SI Slovenia 2036 7.59
SI Slovenia 2022 7.52

and i created two tables

CREATE TABLE `countries` (
      `iso` varchar(2) NOT NULL,
      `iso3` varchar(3) DEFAULT NULL,
      `iso_code` int(11) NOT NULL DEFAULT 0,
      `fips` varchar(2) DEFAULT UNIQUE,
      `display_name` varchar(255) DEFAULT NULL,
      `currency_name` varchar(255) DEFAULT NULL,
      `phone_code` int(11) DEFAULT NULL,
      `region_code` int(11) DEFAULT NULL,
       PRIMARY KEY (`iso`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `birth_rate_stat` (
     `iso` varchar(2) NOT NULL,
     `year` int(11) NOT NULL,
     `crude_birth_rate` float NOT NULL,
     PRIMARY KEY (`iso`,`year`),
     CONSTRAINT `crude_birth_rate_ibfk_1` FOREIGN KEY (`iso`) REFERENCES `countries` (`iso`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

The problem that i face while trying to connect the birth_rate_stat table with the countries one is that, in the birth_rate_stat table, the column country_code contains entries from both the ISO and the FIPS columns from the table countries. That means I can't set iso as a primary key nor fips as fips contains null values. I tried to create a country_id column which would distinguish the countries but then i get an error Cannot add or update a child row: a foreign key constraint fails which makes sense since it doesn't exist in birth_rate_stat.

Do you have some suggestions as to how i could connect the two tables?

1 Answer 1

0

Plan A -- Cleanse the code as you insert it.

That is, decide that all entries will have, say, iso and that fips is optional. There are plenty of online tables that map those two sets of codes; download one of them for your use.

Plan B -- If that is not possible, then change to something like

CREATE TABLE `countries` (
  `id` SMALLINT UNSIGNED NOT NULL,
  `iso` CHAR(2) NOT NULL,
  `iso3` CHAR(3) DEFAULT NULL,
  `iso_code` int(11) NOT NULL DEFAULT 0,
  `fips` CHAR(2) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `currency_name` varchar(255) DEFAULT NULL,
  `phone_code` SMALLINT UNSIGNED DEFAULT NULL,
  `region_code` SMALLINT UNSIGNED DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE(`iso`),
   UNIQUE(`fips`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

Notes:

  • You will have to join on id, not iso.
  • UNIQUE allows multiple rows to have NULL, but PRIMARY KEY does not.
  • I changed several datatypes.
  • Consider changing to utf8mb4 to allow each country to have its own spelling. (Or maybe have another column for such.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.