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

cannot drop column with foreign key constraint

Details

    Description

      CREATE TABLE `tab1` (
      		 
        `tab1id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `tab1desc` varchar(60) NOT NULL,
      		 
        PRIMARY KEY (`tab1id`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
      		 
       
      		 
      CREATE TABLE `tab2` (
      		 
        `tab2id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `tab1id` int(10) unsigned DEFAULT NULL,
      		 
        `tab2desc` varchar(60) NOT NULL,
      		 
        PRIMARY KEY (`tab2id`),
      		 
        KEY `idxtab2tab1id` (`tab1id`),
      		 
        CONSTRAINT `fktab2tab1id` FOREIGN KEY (`tab1id`) REFERENCES `tab1` (`tab1id`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

      dropping tab1id failed:

      alter table tab2 drop COLUMN tab1id;
      		 

      Cannot drop index 'idxtab2tab1id': needed in a foreign key constraint

      I can resolve by drop constraint first, but it is not fine

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          serg Sergei Golubchik added a comment -

          I don't think it's a bug. What behavior do you prefer, for a foreign key to be dropped automatically? Technically, SQL Standard has this behavior, but one needs to use special syntax to invoke it,

          ALTER TABLE tab2 DROP COLUMN tab1id CASCADE

          MariaDB doesn't support this. And when CASCADE is not specified, the foreign key constraint should not be automatically dropped.

          But you can drop both in the same statement, like in

          ALTER TABLE tab2 DROP COLUMN tab1id, DROP FOREIGN KEY fktab2tab1id

          serg Sergei Golubchik added a comment - I don't think it's a bug. What behavior do you prefer, for a foreign key to be dropped automatically? Technically, SQL Standard has this behavior, but one needs to use special syntax to invoke it, ALTER TABLE tab2 DROP COLUMN tab1id CASCADE MariaDB doesn't support this. And when CASCADE is not specified, the foreign key constraint should not be automatically dropped. But you can drop both in the same statement, like in ALTER TABLE tab2 DROP COLUMN tab1id, DROP FOREIGN KEY fktab2tab1id
          svh Stefan van Hasselt added a comment -

          Hi Sergei, on oracle it works, so I wondered. On derby it works also (ok, not a real database).
          But on SQL-Server and Sybase it does not work.
          So, it is just inconvenient but not a bug.
          Thanks for your time and attention. And thanks for best database mariadb
          Stefan

          svh Stefan van Hasselt added a comment - Hi Sergei, on oracle it works, so I wondered. On derby it works also (ok, not a real database). But on SQL-Server and Sybase it does not work. So, it is just inconvenient but not a bug. Thanks for your time and attention. And thanks for best database mariadb Stefan

          People

            serg Sergei Golubchik
            svh Stefan van Hasselt
            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.