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

Some symbols in table name can cause to Error Code: 1050 when created FK

    XMLWordPrintable

Details

    Description

      Reproduce code:

      CREATE TABLE `#departments` (
        `id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id_depart`)
      ) ENGINE=INNODB;
       
      CREATE TABLE `crm_client` (
        `id_client` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id_client`)
      ) ENGINE=INNODB;
       
       
      CREATE TABLE `#departments_pos` (
        `id_pos` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_depart` INT(10) UNSIGNED NOT NULL,
        `id_client_bank` INT(10) UNSIGNED NOT NULL,
        PRIMARY KEY (`id_pos`),
        KEY `id_depart` (`id_depart`),
        CONSTRAINT `#departments_pos_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departments` (`id_depart`)
      ) ENGINE=INNODB;
       
       
      ALTER TABLE `#departments_pos`  
        ADD FOREIGN KEY (`id_client_bank`) REFERENCES `crm_client`(`id_client`);

      Error Code: 1050
      Table './test2/@0023departments_pos' already exists

      Workaround is manual define CONSTRAINT name

      ALTER TABLE `#departments_pos`  
        ADD CONSTRAINT `#departments_pos_ibfk_2` FOREIGN KEY (`id_client_bank`) REFERENCES `crm_client`(`id_client`);

      Yet another example:

      CREATE TABLE `#departaments` (
        `id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id_depart`)
      ) ENGINE=INNODB DEFAULT CHARSET=utf8
       
       
      CREATE TABLE `#departaments_tree` (
        `id_depart` INT(10) UNSIGNED NOT NULL,
        `id_depart_in` INT(10) UNSIGNED NOT NULL,
        PRIMARY KEY (`id_depart`,`id_depart_in`),
        CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`)
      ) ENGINE=INNODB DEFAULT CHARSET=utf8
       
       
      ALTER TABLE `#departaments_tree`  
        ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`)

      Error CODE: 1050
      TABLE './test3/@0023departaments_tree' already EXISTS

      Workaround is manual define CONSTRAINT name

      ALTER TABLE `#departaments_tree`  
        ADD CONSTRAINT `#departaments_tree_ibfk_2` FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`);

      Attachments

        Activity

          People

            jplindst Jan Lindström (Inactive)
            mikhail Mikhail Gavrilov
            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.