Coursal Coursal - 17 days ago 4
MySQL Question

MySQL: errno: 105 can't create table

I wanted to make a database exactly as below:

enter image description here

So I wanted to define the primary and foreign keys for each table.
Based on this answer, I saw that:


  • "city" table has 1 PK (ID) and 1 FK (countrycode)

  • "countrylanguage" table has 2 PK (Language and countrycode) and 1 FK
    (countrycode)

  • "country" table has 1 PK (Code)



So I tried to make some magic on a "pre-heated" code:

CREATE TABLE `City` (
`ID` int(11) NOT NULL,
`Name` varchar(35) NOT NULL ,
`CountryCode` varchar(3) NOT NULL DEFAULT '',
`District` varchar(20) NOT NULL ,
`Population` int(11) NOT NULL ,
PRIMARY KEY(`ID`) ,
FOREIGN KEY(`CountryCode`) REFERENCES `Country`(`Code`)
) ;

CREATE TABLE `CountryLanguage` (
`CountryCode` varchar(3) NOT NULL DEFAULT '',
`Language` varchar(30) NOT NULL ,
`IsOfficial` varchar(30) NOT NULL ,
`Percentage` float(4,1) NOT NULL ,
PRIMARY KEY(`Language`),
FOREIGN KEY(`CountryCode`) REFERENCES `Country`(`Code`)
) ;

CREATE TABLE `Country` (
`Code` varchar(3) NOT NULL DEFAULT '',
`Name` varchar(52) NOT NULL DEFAULT '',
`Continent` varchar(63),
`Region` varchar(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` varchar(45) NOT NULL DEFAULT '',
`GovernmentForm` varchar(45) NOT NULL DEFAULT '',
`HeadOfState` varchar(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` varchar(2) NOT NULL DEFAULT '',
PRIMARY KEY(`Code`)
) ;


but my good ol' mysql command line client has the same ERROR 1005 thing twice, and says that it can't create tables 'test.city' and 'test.countrylanguage'
with the errno:150 thingy as an explanation.

So I searched a bit around here and I found some answers regarding to table elements not having the same type/parameter (f.e. INT(2) to INT(2) NOT NULL). As fas as I could see, Nothing like this happens here.

What is my coffee-drained brain missing here?

Thank you for your time in advance.

Answer

wrong create sequnce you should create firts Country because City and CountryLanguage refer to country table

CREATE TABLE `Country` (
  `Code` varchar(3) NOT NULL DEFAULT '',
  `Name` varchar(52) NOT NULL DEFAULT '',
  `Continent` varchar(63),
  `Region` varchar(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` varchar(45) NOT NULL DEFAULT '',
  `GovernmentForm` varchar(45) NOT NULL DEFAULT '',
  `HeadOfState` varchar(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` varchar(2) NOT NULL DEFAULT '',
  PRIMARY KEY(`Code`)
) ;

  CREATE TABLE `City` (
  `ID` int(11) NOT NULL,
  `Name` varchar(35) NOT NULL ,
  `CountryCode` varchar(3) NOT NULL DEFAULT '',
  `District` varchar(20) NOT NULL ,
  `Population` int(11) NOT NULL ,
  PRIMARY KEY(`ID`) ,
  FOREIGN KEY(`CountryCode`) REFERENCES `Country`(`Code`)
) ;

CREATE TABLE `CountryLanguage` (
  `CountryCode` varchar(3) NOT NULL DEFAULT '',
  `Language` varchar(30) NOT NULL ,
  `IsOfficial` varchar(30) NOT NULL ,
  `Percentage` float(4,1) NOT NULL ,
  PRIMARY KEY(`Language`),
  FOREIGN KEY(`CountryCode`) REFERENCES `Country`(`Code`)
  ) ;