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

ALTER TABLE: DROP CONSTRAINT is ignored when ADD CONSTRAINT is also present

    XMLWordPrintable

Details

    Description

      When an `ALTER TABLE` statement tries to drop an constraint while also adding one, the drop operation is not performed:

       
      MariaDB [test]> create table a (id int primary key);
      Query OK, 0 rows affected (0.016 sec)
       
      MariaDB [test]> create table b (id int primary key references a(id));
      Query OK, 0 rows affected (0.015 sec)
       
      MariaDB [test]> show create table b\G
      *************************** 1. row ***************************
             Table: b
      Create Table: CREATE TABLE `b` (
        `id` int(11) NOT NULL,
        PRIMARY KEY (`id`),
        CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
      1 row in set (0.001 sec)
       
      MariaDB [test]> alter table b drop constraint b_ibfk_1, add constraint other foreign key (id) references a(id) on update cascade;
      Query OK, 0 rows affected (0.023 sec)              
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table b\G
      *************************** 1. row ***************************
             Table: b
      Create Table: CREATE TABLE `b` (
        `id` int(11) NOT NULL,
        PRIMARY KEY (`id`),
        CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`),
        CONSTRAINT `other` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
      1 row in set (0.010 sec)
      

      Attachments

        Issue Links

          Activity

            People

              vlad.lesin Vladislav Lesin
              dotdash Björn Steinbrink
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.