Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19891

CREATE TABLE statement printed by SHOW CREATE TABLE fails with (errno: 150 "Foreign key constraint is incorrectly formed")

    Details

      Description

      When I issue SHOW CREATE TABLE city in the world sample database and take that statement to create an identical table in a different schema I get the error mentioned above.

      MariaDB [world]> show create table city\G
      *************************** 1. row ***************************
             Table: city
      Create Table: CREATE TABLE `city` (
        `ID` int(11) NOT NULL AUTO_INCREMENT,
        `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`),
        KEY `CountryCode` (`CountryCode`),
        CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
      ) ENGINE=InnoDB AUTO_INCREMENT=4095 DEFAULT CHARSET=latin1
      1 row in set (0.000 sec)
       
      MariaDB [world2]> CREATE TABLE `city` (
          ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
          ->   `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`),
          ->   KEY `CountryCode` (`CountryCode`),
          ->   CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
          -> ) ENGINE=InnoDB AUTO_INCREMENT=4095 DEFAULT CHARSET=latin1;
      ERROR 1005 (HY000): Can't create table `world2`.`city` (errno: 150 "Foreign key constraint is incorrectly formed")
      

        Attachments

          Activity

            People

            • Assignee:
              anel Anel Husakovic
              Reporter:
              umoser Ulrich Moser
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: