CREATE TABLE Country (
  Code  char(3) Not Null  Default '',
  Name  char(52) Not Null  Default '',
  Continent  enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') 
                 Not Null  Default 'Asia',
  Region  char(26) Not Null  Default '',
  SurfaceArea  float(10,2) Not Null  Default '0.00',
  IndepYear  smallint(6)  Default NULL,
  Population  int(11) Not Null  Default '0',
  LifeExpectancy  float(3,1)  Default NULL,
  GNP  float(10,2)  Default NULL,
  GNPOld  float(10,2)  Default NULL,
  LocalName  char(45) Not Null  Default '',
  GovernmentForm  char(45) Not Null   Default '',
  HeadOfState  char(60)  Default NULL,
  Capital  int(11)  Default NULL,
  Code2  char(2) Not Null  Default '',
  PRIMARY KEY  (Code)
) ENGINE=InnoDB;
+----------------+---------------------------------------+------+-----+---------+-------+
| Field          | Type                                  |Null | Key | Default | Extra |
+----------------+---------------------------------------+------+-----+---------+-------+
| Code           | char(3)                               |NO   | PRI |         |       |
| Name           | char(52)                              |NO   |     |         |       |
| Continent      | enum('Asia','Europe,...               |NO   |     | Asia    |       |
| Region         | char(26)                              |NO   |     |         |       |
| SurfaceArea    | float(10,2)                           |NO   |     | 0.00    |       |
| IndepYear      | smallint(6)                           |YES  |     | NULL    |       |
| Population     | int(11)                               |NO   |     | 0       |       |
| LifeExpectancy | float(3,1)                            |YES  |     | NULL    |       |
| GNP            | float(10,2)                           |YES  |     | NULL    |       |
| GNPOld         | float(10,2)                           |YES  |     | NULL    |       |
| LocalName      | char(45)                              |NO   |     |         |       |
| GovernmentForm | char(45)                              |NO   |     |         |       |
| HeadOfState    | char(60)                              |YES  |     | NULL    |       |
| Capital        | int(11)                               |YES  |     | NULL    |       |
| Code2          | char(2)                               |NO   |     |         |       |
+----------------+---------------------------------------+------+-----+---------+-------+

show create table country:
  ...
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB 



CREATE TABLE City (
  ID  int Not Null,
  Name  char(35) Not Null  Default '',
  CountryCode  char(3) Not Null  Default '',
  District  char(20) Not Null  Default '',
  Population  int(11) Not Null  Default '0',
  PRIMARY KEY (ID),
  FOREIGN KEY(CountryCode) REFERENCES Country(Code)
) ENGINE=InnoDB;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| ID          | int(11)  | NO   | PRI | NULL    |       |
| Name        | char(35) | NO   |     |         |       |
| CountryCode | char(3)  | NO   | MUL |         |       |
| District    | char(20) | NO   |     |         |       |
| Population  | int(11)  | NO   |     | 0       |       |
+-------------+----------+------+-----+---------+-------+

show create table city:
  ...
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB 


CREATE TABLE CountryLanguage (
  CountryCode  char(3) Not Null  Default '',
  Language  char(30) Not Null  Default '',
  IsOfficial  enum('T','F') Not Null  Default 'F',
  Percentage  float(4,1) Not Null  Default '0.0',
  PRIMARY KEY  (CountryCode,Language),
  FOREIGN KEY(CountryCode) REFERENCES Country(Code)
) ENGINE=InnoDB;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+

show create table countrylanguage:
  ...
  PRIMARY KEY (`CountryCode`,`Language`),
  CONSTRAINT `countrylanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB 

******************************************************************************
Errors:
Can not Insert a row into child table whose foreign key value does not exist in parent table primary key:
  (foreign key is invalid, does not exist in parent Country table)
mysql> Insert into countrylanguage values('XXX','English','Y',50);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`worldfk`.`countrylan
guage`, CONSTRAINT `countrylanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))

mysql> Insert into city values(12345, 'Chatan','XXX','Okinawa',50000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`worldfk`.`city`, CON
STRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))

Can not Update a row of child table to a foreign key value that does not exist in parent table primary key:
mysql> Update city set countrycode='XXX' where name='Kabul';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`worldfk`.`city`, CON
STRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))

By default, can not Delete a row of parent table if its primary key is in the foreign
key of any child table:
mysql> delete from country where code='AFG';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`worldfk`.`city`,
 CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))

By default, can not Update primary key of parent if that value exists as foreign key value in
any child table.
mysql> Update country set code='XXX' where code='AFG';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`worldfk`.`city`,
 CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))