Thank you! I repeated as described on MariaDB 10.4-11.0

The native ALTER TABLE in InnoDB seems to work fine, at least in MariaDB Server 10.6, where I tested the following:

--source include/have_innodb.inc

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t2 (id INT PRIMARY KEY REFERENCES t1(id)) ENGINE=InnoDB;

SHOW CREATE TABLE t2;

SET foreign_key_checks=0;

ALTER TABLE t2

DROP CONSTRAINT t2_ibfk_1,

ADD CONSTRAINT other FOREIGN KEY (id) REFERENCES a(id) ON UPDATE CASCADE;

SHOW CREATE TABLE t2;

DROP TABLE t2,t1;

If I add , ALGORITHM=COPY to the ALTER TABLE statement, I will get two foreign key constraints. For some reason, on the revision of 10.6 that I tested, if I omit the SET statement, the ALTER TABLE will fail as follows:

ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")

The ALGORITHM=COPY version of the operation not only fails to drop the constraint; it will also unnecessarily rebuild the table and copy all data, until MDEV-16356 is fixed.

It actually gets funnier: When I wrote the mtr version of the test, I intended to rename the tables a and b to t1 and t2, respectively. But, I forgot to replace one reference. Because of foreign_key_checks=0, the reference to the missing table a will be allowed.

Here is a better test:

--source include/have_innodb.inc

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t2 (id INT PRIMARY KEY REFERENCES t1(id)) ENGINE=InnoDB;

SET foreign_key_checks=0;

ALTER TABLE t2

DROP CONSTRAINT t2_ibfk_1,

ADD CONSTRAINT other FOREIGN KEY (id) REFERENCES t1(id) ON UPDATE CASCADE,

ALGORITHM=NOCOPY;

SHOW CREATE TABLE t2;

SET foreign_key_checks=1;

ALTER TABLE t2

DROP CONSTRAINT other,

ADD CONSTRAINT another FOREIGN KEY (id) REFERENCES t1(id) ON DELETE CASCADE,

ALGORITHM=COPY;

SHOW CREATE TABLE t2;

DROP TABLE t2,t1;

The ALGORITHM clauses are redundant, only there to show what is going on. The first ALTER TABLE works correctly; the second one will wrongly end up with two constraints.

If I change the test to use DROP FOREIGN KEY instead of DROP CONSTRAINT, the constraint will be dropped.

MDEV-20480 was implemented incompletely, because it failed to remove or replace dict_foreign_parse_drop_constraints().

The ad-hoc parser in dict_foreign_parse_drop_constraints() looks for DROP followed by FOREIGN KEY. It fails to detect DROP CONSTRAINT or DROP CONSTRAINT IF EXISTS.

Also in this case, the SQL layer is definitely validating the constraint names, and InnoDB is duplicating some of that work. I think that dict_foreign_parse_drop_constraints() must be replaced with something that obtains the necessary information from the SQL layer, just like the native ALTER TABLE code path does.