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

Can't drop column for which a foreign key exists

    XMLWordPrintable

Details

    Description

      Hi!

      In MariaDB, it's impossible to drop a column which references another table with a foreign key - i.e. not the REFERENCED column, but the one that REFERENCES other table. I find it strange - why MariaDB doesn't just drop the FK along with the dropped column?

      > create table test1 (id int not null auto_increment primary key);
      Query OK, 0 rows affected (0.01 sec)
      > create table test2 (id int not null auto_increment primary key, b int, foreign key (b) references test1 (id) on update cascade);
      Query OK, 0 rows affected (0.01 sec)
      > alter table test2 drop b;
      ERROR 1025 (HY000): Error on rename of './bugs3/#sql-4517_280f4' to './bugs3/test2' (errno: 150)
      > show engine innodb status\G

      ------------------------
      LATEST FOREIGN KEY ERROR
      ------------------------
      141021 18:25:23 Error in foreign key constraint of table bugs3/test2:
      there is no index in the table which would contain
      the columns as the first columns, or the data types in the
      table do not match the ones in the referenced table
      or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
      ,
        CONSTRAINT "test2_ibfk_1" FOREIGN KEY ("b") REFERENCES "test1" ("id") ON UPDATE CASCADE

      Attachments

        Activity

          People

            Unassigned Unassigned
            vitalif Vitaliy Filippov
            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.