mysql - 当链接为外键时，将主键从 BigInt 更改为 Unsigned BigInt

Question

我有这样一个场景:

CREATE TABLE `Users` (
  `IdUser` bigint(20) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `MainTable` (
  `IdLite` bigint(20) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `LinkedTable` (
  `IdUser` bigint(20) NOT NULL,
  `IdLite` bigint(20) NOT NULL,
PRIMARY KEY (`IdUser`, `IdLite`),
FOREIGN KEY (`IdUser`) REFERENCES `Users` (`IdUser`),
FOREIGN KEY (`IdLite`) REFERENCES `MainTable` (`IdLite`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在尝试使用如下查询将 IdLite 更改为 Unsigned:

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `MainTable` CHANGE `IdLite` 
    `IdLite` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `LinkedTable` CHANGE `IdLite` 
    `IdLite` BIGINT(20) UNSIGNED NOT NULL;
SET FOREIGN_KEY_CHECKS=1;

但我得到错误:

errno: 150 - Foreign key constraint is incorrectly formed

我该如何解决？

Answer 1

您不能更改现有 FK 约束中使用的列的数据类型。

您可以删除 FK 约束，更改列数据类型，然后重新创建 FK 约束:

ALTER TABLE LinkedTable
  DROP FOREIGN KEY linkedtable_ibfk_2; -- or whatever the symbol is named

ALTER TABLE MainTable
  MODIFY IdLite SERIAL; -- alias of BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

ALTER TABLE LinkedTable
  MODIFY IdLite BIGINT UNSIGNED NOT NULL,
  ADD FOREIGN KEY (IdLite) REFERENCES MainTable (IdLite);