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

    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

            • Assignee:
              jplindst Jan Lindström
              Reporter:
              mikhail Mikhail Gavrilov
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: