Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.19, 10.11.9
-
None
-
None
Description
CREATE TABLE `t` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`x` int(10) unsigned DEFAULT NULL, |
`y` int(10) unsigned DEFAULT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `y` (`y`) USING HASH, |
KEY `fk_1` (`x`), |
CONSTRAINT `fk_1` FOREIGN KEY (`x`) REFERENCES `t` (`id`) |
) ENGINE=InnoDB;
|
/* there are some random rows inserted in the table */
|
|
|
> alter table t DROP CONSTRAINT `fk_1`; |
Query OK, 10001 rows affected (0.287 sec) |
Records: 10001 Duplicates: 0 Warnings: 0
|
|
|
> SHOW CREATE TABLE t\G |
*************************** 1. row ***************************
|
Table: t |
Create Table: CREATE TABLE `t` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`x` int(10) unsigned DEFAULT NULL, |
`y` int(10) unsigned DEFAULT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `y` (`y`), |
KEY `fk_1` (`x`), |
CONSTRAINT `fk_1` FOREIGN KEY (`x`) REFERENCES `t` (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=16387 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
|
Note that the constraint fk_1 is still there, but the UNIQUE KEY y is now BTREE instead of HASH. This last part does not always happen. It seems it somehow depends on the particular indexes.
The result
Query OK, 10001 rows affected (0.287 sec)
|
suggests that some or all indexes are rebuilt, without removing the FK constraint.
When there is no such HASH unique key ALTER ... DROP CONSTRAINT reports 0 rows affected and FK is removed.
ALTER TABLE DROP FOREIGN KEY `fk_1`; |
always removes the constraint, but again sometimes it rebuilds that HASH key as BTREE
Also