mysql使用外键将int int列改为bigint

I want to change the datatype of some primary-key columns in my database from INT to BIGINT. The following definition is a toy-example to illustrate the problem:

我想将数据库中某些主键列的数据类型从INT更改为BIGINT。以下定义是一个玩具示例来说明问题：

CREATE TABLE IF NOT EXISTS `owner` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `thing_id` (`thing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `thing`;
CREATE TABLE IF NOT EXISTS `thing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `owner`
  ADD CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`thing_id`) REFERENCES `thing` (`id`);

Now when i try to execut one of the following commands:

现在，当我尝试执行以下命令之一时：

ALTER TABLE `thing` CHANGE `id` `id` BIGINT NOT NULL AUTO_INCREMENT;
ALTER TABLE `owner` CHANGE `thing_id` `thing_id` BIGINT NOT NULL;

i'm running into an error

我遇到了一个错误

#1025 - Error on rename of './debug/#[temp-name]' to './debug/[tablename]' (errno: 150)

SHOW INODB STATUS outputs:

显示INODB状态输出：

LATEST FOREIGN KEY ERROR
------------------------
120126 13:34:03 Error in foreign key constraint of table debug/owner:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
 CONSTRAINT "owner_ibfk_1" FOREIGN KEY ("thing_id") REFERENCES "thing" ("id")

I'm guessing that the foreign key definition blocks changing the column type on either side. The naive approach to solve this problem would be to delete the foreign key definitions, alter the columns and re-define the foreign keys. is there a better solution?

我猜测外键定义会阻止更改任一侧的列类型。解决此问题的天真方法是删除外键定义，更改列并重新定义外键。有更好的解决方案吗？

3 个解决方案

#1

Even with SET foreign_key_checks = 0, you can't alter the type of the constraint column. From MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

即使使用SET foreign_key_checks = 0，也无法更改约束列的类型。来自MySQL doc：http：//dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

However, even if foreign_key_checks = 0, InnoDB does not permit the creation of a foreign key constraint where a column references a nonmatching column type.

但是，即使foreign_key_checks = 0，InnoDB也不允许创建外键约束，其中列引用不匹配的列类型。

So, I agree with the comment of Devart. Just drop it and create it again.

所以，我同意Devart的评论。只需将其删除并再次创建即可。

#2

I could suggest you to rename such fields in GUI tool - dbForge Studio for MySQL (free trial edition):

我建议你在GUI工具中重命名这些字段 - dbForge Studio for MySQL（免费试用版）：

Just select the field you want to rename in the Database Explorer, click on Refactoring->Rename command, enter new name in openned window, and press OK, it will rename the field and recreate all foreign keys automatically.

只需在数据库资源管理器中选择要重命名的字段，单击重构 - >重命名命令，在打开的窗口中输入新名称，然后按确定，它将重命名该字段并自动重新创建所有外键。

#3

I had a similar problem the solution is the change clause:

我有一个类似的问题，解决方案是更改条款：

ALTER TABLE table_name CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT;

That worked for me.

这对我有用。

#1