MySQL Bugs: #113226: ALTER TABLE with comma separated DROP FOREIGN KEY AND ADD FOREIGN KEY throws

Bug #113226	ALTER TABLE with comma separated DROP FOREIGN KEY AND ADD FOREIGN KEY throws
Submitted:	27 Nov 2023 2:00	Modified:	27 Nov 2023 12:56
Reporter:	Ako Tulu	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.33-25	OS:	Ubuntu (22.04.3 LTS)
Assigned to:		CPU Architecture:	x86

Description:
When using ALTER TABLE comma separated command extension to standard SQL, it fails do drop the `FOREIGN KEY` and `ADD FOREIGN KEY` with error `DUPLICATE`.

How to repeat:
CREATE TABLE `table_1`
(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    CONSTRAINT `pk_table_1` PRIMARY KEY (`id`)
);

CREATE TABLE `table_2`
(
    `id` int(10) unsigned NOT NULL,
    CONSTRAINT `fk_table_2_id` FOREIGN KEY (`id`) REFERENCES `table_1` (`id`)
);

ALTER TABLE `table_2` DROP FOREIGN KEY `fk_table_2_id`, ADD CONSTRAINT `fk_table_2_id` FOREIGN KEY (`id`) REFERENCES `table_1` (`id`) ON UPDATE CASCADE;

Suggested fix:
Command should succeed without ERROR 1826 (HY000): Duplicate foreign key constraint name 'fk_table_2_id'.

Hi Mr. Tulu,

Thank you for your bug report.

We managed to achieve success with separating the ALTER TABLE in two commands. Hence, this is definitely a bug.

However, your bug is a duplicate of an already verified bug:

https://bugs.mysql.com/bug.php?id=68286

We do not know when will this be fixed.

Duplicate.