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

ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS does not work if constraint name is not used

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2
    • Fix Version/s: 10.0.8
    • Component/s: None
    • Labels:
      None

      Description

      ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS creates index on the given column using the key id provided but that name is not the same as constraint name (at least on InnoDB). That makes this feature unusable (and some cases to crash).

      Problem is that MySQL does not really know foreign key constraint names, it knows key names. Thus

      alter table t2 add constraint f foreign key if not exists (i) references t1(pk)

      Would create foreign key constraint f on InnoDB data dictionary and MySQL key f on MySQL data dictionary. But

      alter table t2 add foreign key if not exists f(i) references t1(pk);

      Would create foreign key constraint t2_ibfk_1 on InnoDB data dictionary and MySQL key f on MySQL data dictionary. In this case you can't do:

      alter table t2 drop foreign key if exists t2_ibfk_1;

      Because, there is no key t2_ibfk_1 on MySQL data dictionary, and

      alter table t2 drop foreign key if exists f;

      would not also work because there is no foreign key constraint f on InnoDB data dictionary while there is a key f on MySQL. There is no real test cases on mysql-test suite for adding/dropping foreign keys with if [not] exists feature.

      Test case:

      create table t1 (pk int primary key) engine=InnoDB;
      create table t2 (i int) engine=InnoDB;
      alter table t2 add foreign key if not exists f(i) references t1(pk);
      show create table t2;
      alter table t2 drop foreign key if exists f;
      show warnings;
      alter table t2 drop foreign key if exists t2_ibfk_1;
      show warnings;
      show create table t2;
      drop table t2,t1;
      create table t1 (pk int primary key) engine=InnoDB;
      create table t2 (i int) engine=InnoDB;
      alter table t2 add constraint f foreign key if not exists (i) references t1(pk);
      show create table t2;
      alter table t2 drop foreign key if exists f;
      show warnings;
      show create table t2;
      alter table t2 add constraint f foreign key if not exists (i) references t1(pk);
      show create table t2;
      alter table t2 add constraint f foreign key if not exists (i) references t1(pk);
      show warnings;
      show create table t2;
      drop table t2,t1;

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                holyfoot Alexey Botchkov
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: