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

Foreign key implicit index inconsistency

    XMLWordPrintable

    Details

      Description

      MariaDB and MySQL create an 'implicit index', for a foreign key whose columns are not part of an index. That implicit index is removed once the client adds an actual index on the columns involved. However, when the reverse is carried out, i.e. the client removes his actual index again, no implicit index is put back and this causes an error message: Cannot drop index: needed in a foreign key constraint. This is inconvenient for scripts that attempt to reverse actions automatically and there does not seem to be a reason that justifies the inconsistency.

      Test script:

      	CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
      	CREATE TABLE b (id INT NOT NULL PRIMARY KEY);
      	ALTER TABLE b ADD COLUMN a_id INT NOT NULL, ADD CONSTRAINT a_id FOREIGN KEY (a_id) REFERENCES a (id);
      	ALTER TABLE b ADD INDEX a_id (a_id);
      

      Now try to reverse these actions:

      	ALTER TABLE b DROP INDEX a_id;
      	ALTER TABLE b DROP COLUMN a_id, DROP FOREIGN KEY a_id;
      	DROP TABLE b;
      	DROP TABLE a;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              user2180613 Remy Fox
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration