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

[HY000][1553] Changing name of primary key column with foreign key constraint fails.

Details

    Description

      Changing the name of a primary key column with a foreign key constraint fails with the following error since 10.4.13:

      [HY000][1553] (conn=17) Cannot drop index 'PRIMARY': needed in a foreign key constraint

      create schema test collate utf8_unicode_ci;
      		 
      create table test.input_type
      		 
      (
      		 
          input_type_id int(11) unsigned auto_increment
      		 
              primary key
      		 
      );
      		 
       
      		 
      create table test.input
      		 
      (
      		 
          input_id int unsigned auto_increment
      		 
              primary key,
      		 
          input_type_id int unsigned not null,
      		 
          constraint ibfk
      		 
              foreign key (input_type_id) references test.input_type (input_type_id)
      		 
      );
      		 
       
      		 
      create index type_id
      		 
          on test.input (input_type_id);
      		 
       
      		 
      alter table test.input_type change input_type_id id int(11) unsigned auto_increment;

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23950 Renaming a column that is used as a foreign key throws an error

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            mac89 Maarten Manders created issue -
            mac89 Maarten Manders made changes -
            Field Original Value New Value
            Description Changing the name of a primary key column with a foreign key constraint fails with the following error since 10.4.13:

            [HY000][1553] (conn=17) Cannot drop index 'PRIMARY': needed in a foreign key constraint

            {code:mysql}
            create schema test collate utf8_unicode_ci;
            create table test.input_type
            (
                input_type_id int(11) unsigned auto_increment
                    primary key
            );

            create table test.input
            (
                input_id int unsigned auto_increment
                    primary key,
                input_type_id int unsigned not null,
                constraint ibfk
                    foreign key (input_type_id) references test.input_type (input_type_id)
            );

            create index type_id
                on test.input (input_type_id);

            alter table test.input_type change input_type_id id int(11) unsigned auto_increment;
            {code}
            		Changing the name of a primary key column with a foreign key constraint fails with the following error since 10.4.13:

            +[HY000][1553] (conn=17) Cannot drop index 'PRIMARY': needed in a foreign key constraint+

            {code:mysql}
            create schema test collate utf8_unicode_ci;
            create table test.input_type
            (
                input_type_id int(11) unsigned auto_increment
                    primary key
            );

            create table test.input
            (
                input_id int unsigned auto_increment
                    primary key,
                input_type_id int unsigned not null,
                constraint ibfk
                    foreign key (input_type_id) references test.input_type (input_type_id)
            );

            create index type_id
                on test.input (input_type_id);

            alter table test.input_type change input_type_id id int(11) unsigned auto_increment;
            {code}
            alice Alice Sherepa made changes -
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            alice Alice Sherepa made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Assignee Marko Mäkelä [ marko ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Labels regression
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            Assignee Marko Mäkelä [ marko ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2021-04-07 17:46:15.0 2021-04-07 17:46:15.366
            bar Alexander Barkov made changes -
            Fix Version/s 10.4.19 [ 25205 ]
            Fix Version/s 10.5.10 [ 25204 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 109390 ] MariaDB v4 [ 157892 ]

            People

              bar Alexander Barkov
              mac89 Maarten Manders
              Votes:
              6 Vote for this issue
              Watchers:
              13 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.