Coursal Coursal - 8 months ago 77
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 Source

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`)
  ) ;