MySQL自增主键与级联更新外键的字段类型变更问题

问题 #

在InnoDB引擎下，存在如下表结构：

CREATE TABLE `reports` (
  `report_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`report_id`),
);

CREATE TABLE `options` (
  `option_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id_fk` int(11) NOT NULL,
  ...
  PRIMARY KEY (`option_id`),
  CONSTRAINT `options_ibfk_1` FOREIGN KEY (`report_id_fk`) REFERENCES `reports` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

reports与options为一对一关系，需要将report_id字段从INT类型修改为BIGINT类型。原以为子表options的外键配置了ON UPDATE CASCADE会自动同步父表的更新操作，于是执行了以下语句：

ALTER TABLE reports DROP PRIMARY KEY, MODIFY COLUMN report_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT;

但触发了外键约束错误，报错信息如下：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
230612 11:51:05 Error in foreign key constraint of table [DB name]/options:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "options_ibfk" FOREIGN KEY ("report_id_fk") REFERENCES "reports" ("report_id") ON DELETE CASCADE ON UPDATE CASCADE
The index in the foreign key in table is "report_id_fk"

想确认：能否在不临时删除子表options外键约束的前提下，修改父表reports的主键字段类型？需要保留ON DELETE CASCADE配置，且有多个子表以相同方式引用report_id。

回答 #

不行，没办法直接在保留外键约束生效的状态下修改父表主键类型，但你不需要删除外键约束，只需要临时禁用外键检查，按顺序修改子表和父表的字段类型即可，操作步骤如下：

1. 临时禁用外键约束检查（生产环境建议在业务低峰期操作，避免数据不一致）：

SET FOREIGN_KEY_CHECKS = 0;

2. 对所有引用reports.report_id的子表，将外键字段修改为与目标类型一致的BIGINT UNSIGNED：

ALTER TABLE options MODIFY COLUMN report_id_fk BIGINT UNSIGNED NOT NULL;
-- 其他引用该主键的子表执行相同逻辑的ALTER语句

3. 修改父表reports的主键字段类型（无需先删除主键再重建，直接MODIFY即可保留自增和主键属性）：

ALTER TABLE reports MODIFY COLUMN report_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

4. 恢复外键约束检查：

SET FOREIGN_KEY_CHECKS = 1;

报错原因说明 #

你之前的操作之所以报错，是因为InnoDB的外键约束要求父表被引用字段和子表外键字段的类型必须完全一致（包括数据类型、长度、是否无符号等属性）。你试图直接修改父表主键为BIGINT UNSIGNED，但此时子表的report_id_fk还是int(11)，类型不匹配，外键约束会直接阻止这个操作。另外，ON UPDATE CASCADE只负责同步字段的值更新，不处理字段类型的变更。

内容的提问来源于stack exchange，提问作者Jorge Lazo