MySQL更改外键的类型 [英] MySQL change type of foreign key

问题描述

我正在使用MySQL，我有一个索引表，在许多其他表中用作外键。我想改变索引的数据类型（从签名到无符号整数），最好的办法是什么？

我试着改变数据类型索引字段，但由于它被用作其他表的外键而失败。我尝试更改其中一个外键的数据类型，但由于它与索引的数据类型不匹配而失败。

我想我可以手动删除所有的外键约束，改变数据类型并添加约束条件，但这将是一个很大的工作，因为我有很多使用这个索引作为外键的表。有没有办法在进行更改时暂时关闭外键约束？另外，有没有办法将所有引用索引的字段列表作为外键？

更新：
我试图关闭外键检查后修改一个外键，但似乎并没有关闭检查：

pre $ SET foreign_key_checks = 0;

ALTER TABLE`escolaterrafir`.`t23_aluno` MODIFY COLUMN`a21_saida_id` INTEGER DEFAULT NULL;


以下是错误：

  ------------------------ 
最新的外键错误
 ------- ----------------- 
 090506 11:57:34表escolaterrafir / t23_aluno的外键约束错误：
表中没有索引将包含
列作为第一列，或
表中的数据类型与引用表
中的数据类型不匹配，或者其中一个ON ... SET NULL列为声明NOT NULL。约束：
，
 CONSTRAINT FK_t23_aluno_8 FOREIGN KEY（a21_saida_id）REFERENCES t21_turma（A21_ID）
  

<索引表的定义：

pre $ DROP TABLE IF EXASTS`escolaterrafir`.`t21_turma`;
CREATE TABLE`escolaterrafir`.`t21_turma`（
`A21_ID` int（10）unsigned NOT NULL auto_increment，
...
）ENGINE = InnoDB AUTO_INCREMENT = 51 DEFAULT CHARSET = LATIN1;


以及具有指向它的外键的表：

  DROP TABLE IF EXASTS`escolaterrafir`.`t23_aluno`; 
 CREATE TABLE`escolaterrafir```t23_aluno`（
 ... 
`a21_saida_id` int（10）unsigned default NULL，
 ... 
 KEY`Index_7 （``a23_id_pedagogica`），
 ... 
 CONSTRAINT`FK_t23_aluno_8` FOREIGN KEY（`a21_saida_id`）REFERENCES`t21_turma`（`A21_ID`）
）ENGINE = InnoDB AUTO_INCREMENT = 387 DEFAULT CHARSET = LATIN1; 
  

解决方案

为了回答我自己的问题，我找不到更简单的方法来做到这一点。我结束了所有的外键约束，改变字段类型，然后添加所有的外键约束。

正如R. Bemrose指出，使用 SET foreign_key_checks = 0; 只在添加或更改数据时有帮助，但不允许 ALTER TABLE 命令打破外键约束。

I am using MySQL and I have a table with an index that is used as a foreign key in many other tables. I want to change the data type of the index (from signed to unsigned integer) , what is the best way to do this?

I tried altering the data type on the index field, but that fails because it is being used as a foreign key for other tables. I tried altering the data type on one of the foreign keys, but that failed because it didn't match the data type of the index.

I suppose that I could manually drop all of the foreign key constraints, change the data types and add the constraints back, but this would be a lot of work because I have a lot of tables using this index as a foreign key. Is there a way to turn off foreign key constraints temporarily while making a change? Also, is there a way to get a list of all the fields referencing the index as a foreign key?

Update: I tried modifying the one foreign key after turning off foreign key checks, but it doesn't seem to be turning off the checks:

SET foreign_key_checks = 0;

ALTER TABLE `escolaterrafir`.`t23_aluno` MODIFY COLUMN `a21_saida_id` INTEGER DEFAULT NULL;

Here's the error:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
090506 11:57:34 Error in foreign key constraint of table escolaterrafir/t23_aluno:
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 to the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT FK_t23_aluno_8 FOREIGN KEY (a21_saida_id) REFERENCES t21_turma (A21_ID)

Definition of the index table:

DROP TABLE IF EXISTS `escolaterrafir`.`t21_turma`;
CREATE TABLE  `escolaterrafir`.`t21_turma` (
  `A21_ID` int(10) unsigned NOT NULL auto_increment,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;

and the table that has the foreign key that points to it:

DROP TABLE IF EXISTS `escolaterrafir`.`t23_aluno`;
CREATE TABLE  `escolaterrafir`.`t23_aluno` (
  ...
  `a21_saida_id` int(10) unsigned default NULL,
  ...
  KEY `Index_7` (`a23_id_pedagogica`),
  ...
  CONSTRAINT `FK_t23_aluno_8` FOREIGN KEY (`a21_saida_id`) REFERENCES `t21_turma` (`A21_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=latin1;

解决方案

To answer my own question, I could not discover a simpler way to do this. I ended up dropping all the foreign key constraints, changing the field types and then adding all the foreign key constraints back.

As R. Bemrose noted, using SET foreign_key_checks = 0; only helps when adding or changing data, but doesn't allow ALTER TABLE commands that would break foreign key constraints.

这篇关于MySQL更改外键的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！