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

Renaming a column that is used as a foreign key throws an error

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.5.4, 10.5.5, 10.5.6
    • N/A
    • N/A
    • None
    • Docker, mariadb/latest

    Description

      Renaming a column (usually the primary key) that is used as a foreign key in another table with ALTER TABLE syntax (not the new RENAME COLUMN syntax from 10.5.2) causes a Cannot drop index 'PRIMARY': needed in a foreign key constraint error.

      For example, say you have these two tables:

      create table `test` (
      	`testId` int unsigned not null auto_increment primary key,
      	`name` varchar(255) not null
      );
       
      create table `test_ref` (
      	`id` int unsigned not null auto_increment primary key,
      	`refId` int unsigned not null,
      	`name` varchar(255) not null
      );
      

      And you add a foreign key constraint:

      alter table `test_ref`
      add constraint `test_ref_id`
      foreign key (`refId`)
      references `test` (`testId`);
      

      And then try to rename test.testId to test.id with this syntax:

      ALTER TABLE test
      CHANGE testId id INT UNSIGNED AUTO_INCREMENT NOT NULL;
      

      You will get the error mentioned.

      Turning off foreign key checks before and after this alter statement will make it work though, and from what I can tell the references in information_schema.KEY_COLUMN_USAGE is correctly updated, but it does not feel right.

      Also, if using the more recent RENAME COLUMN syntax, the change goes through without any errors. The problem is that this syntax is fairly new and likely not implemented in a lot of libraries yet, like doctrine/dbal for instance.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              karly Alexander Karlstad
              Votes:
              2 Vote for this issue
              Watchers:
              3 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.