链接为外键时，将主键从BigInt更改为Unsigned BigInt [英] Change Primary Key from BigInt to Unsigned BigInt when linked as foreign key

问题描述

我有这样的情况:

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:

I'm trying to change IdLite to Unsigned with a query like this:

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-外键约束格式不正确

errno: 150 - Foreign key constraint is incorrectly formed

我该如何解决?

推荐答案

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

You can't change the data type of columns used in an existing FK constraint.

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

You can drop the FK constraint, change the column data types and then recreate the FK constraint:

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);

这篇关于链接为外键时，将主键从BigInt更改为Unsigned BigInt的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！