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

ALTER TABLE DROP CONSTRAINT does not work for foreign keys

    XMLWordPrintable

Details

    Description

      The syntax is supported in MariaDB 10.2.1 (https://mariadb.com/kb/en/library/alter-table/#drop-constraint), but it doesn't work for foreign key constraints:

      MariaDB [test]> create table tpk(id int primary key, c1 int);
      Query OK, 0 rows affected (0.646 sec)
       
      MariaDB [test]> create table tfk(id int primary key, c1 int, constraint `_________fk_transaction_111_11111_1111_111_id` FOREIGN KEY (`c1`) REFERENCES tpk (`id`)
       ON DELETE NO ACTION ON UPDATE NO ACTION);
      Query OK, 0 rows affected (0.416 sec)
       
      MariaDB [test]> show create table tfk\G
      *************************** 1. row ***************************
             Table: tfk
      Create Table: CREATE TABLE `tfk` (
        `id` int(11) NOT NULL,
        `c1` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `_________fk_transaction_111_11111_1111_111_id` (`c1`),
        CONSTRAINT `_________fk_transaction_111_11111_1111_111_id` FOREIGN KEY (`c1`)
      REFERENCES `tpk` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.007 sec)
       
      MariaDB [test]> alter table tfk drop constraint `_________fk_transaction_111_111
      11_1111_111_id`;
      ERROR 1091 (42000): Can't DROP CONSTRAINT `_________fk_transaction_111_11111_111
      1_111_id`; check that it exists
      MariaDB [test]> alter table tfk drop foreign key `_________fk_transaction_111_11
      111_1111_111_id`;
      Query OK, 0 rows affected (0.129 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      See also MDEV-14873.

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.