mysql将int列更改为带有外键的bigint [英] mysql alter int column to bigint with foreign keys

问题描述

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

CREATE TABLE IF NOT EXISTS` （$ b $ 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如果存在`thing`;
CREATE TABLE IF NOT EXISTS（
`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`）;


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

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


我遇到错误

 ＃1025  - 将'./debug/#[temp-name]'重命名为'./debug/[tablename]'（errno：150）
 < 
 
 
 $显示INODB状态输出：
 
 
 最新的外键错误
 ------------------------ 
 120126 13:34:03外键约束错误表调试/所有者：
表中没有索引，其中包含
列作为第一列，或者
表中的数据类型与引用表中的数据类型不匹配
或其中一个ON ... SET NULL列被声明为NOT NULL。约束：
，
 CONSTRAINTowner_ibfk_1FOREIGN KEY（thing_id）REFERENCESthing（id）
  
我猜猜外键定义块会改变任何一边的列类型。解决这个问题的简单方法是删除外键定义，修改列并重新定义外键。有没有更好的解决方案？
解决方案 
即使使用 SET foreign_key_checks = 0  ，你不能改变约束列的类型。 
从MySQL文档： http：/ /dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html  
 
 
 
 然而，即使foreign_key_checks = 0，InnoDB也不允许创建一个列引用不匹配列类型的外键约束。 
 
 
 
 ，我同意Devart的评论。只需放下它，然后重新创建。
 
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:
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:
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?
解决方案
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

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.

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

                        
这篇关于mysql将int列更改为带有外键的bigint的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！