· 5 years ago · Sep 11, 2020, 06:38 PM
1CREATE TABLE IF NOT EXISTS countryCapital (
2 countryCode CHAR(3) NOT NULL,
3 cityID INT NOT NULL,
4 PRIMARY KEY (countryCode, cityID),
5 FOREIGN KEY (countryCode) REFERENCES country (Code),
6 FOREIGN KEY (cityID) REFERENCES city (ID)
7) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
8
9INSERT INTO countryCapital (countryCode, cityID)
10SELECT country.Code, country.Capital
11FROM country
12WHERE Capital IS NOT NULL;