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

Table disappear after two alter table command

    XMLWordPrintable

Details

    Description

      I create three tables:

      CREATE TABLE `parent1` (
        `id1` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id1`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE `parent2` (
        `id2` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id2`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE `child` (
        `id1` smallint(5) unsigned NOT NULL,
        `id2` smallint(5) unsigned NOT NULL,
        PRIMARY KEY (`id1`,`id2`),
        UNIQUE KEY `id2` (`id2`) USING BTREE,
        CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `parent2` (`id2`) ON DELETE CASCADE,
        CONSTRAINT `child_ibfk_2` FOREIGN KEY (`id1`) REFERENCES `parent1` (`id1`) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      

      and running theese commands:

      MariaDB [LUFI]> SET FOREIGN_KEY_CHECKS=0;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [LUFI]> ALTER TABLE `child` DROP PRIMARY KEY;
      Query OK, 0 rows affected (0.276 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [LUFI]> ALTER TABLE `child` DROP INDEX `id2`;
      ERROR 1025 (HY000): Error on rename of './LUFI/#sql-511_321' to './LUFI/child' (errno: 150 "Foreign key constraint is incorrectly formed")
      MariaDB [LUFI]> SHOW TABLES; 
      +----------------+
      | Tables_in_LUFI |
      +----------------+
      | parent1        |
      | parent2        |
      +----------------+
      2 rows in set (0.002 sec)
       
      MariaDB [LUFI]> CREATE TABLE `child` (
          ->   `id` smallint(5) unsigned NOT NULL
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      ERROR 1005 (HY000): Can't create table `LUFI`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
      MariaDB [LUFI]> 
      

      As seen the table "child" disappeared, and can't recreate.

      Attachments

        Activity

          People

            thiru Thirunarayanan Balathandayuthapani
            LaySoft Lay András
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.