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")

    XMLWordPrintable

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

            anel Anel Husakovic
            umoser Ulrich Moser (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.