代码之家 › 专栏 › 技术社区 › gerdemb

MySQL更改外键的类型

foreign-keys mysql sql

gerdemb · 技术社区 · 17 年前

我使用的是MySQL,我有一个带有索引的表,在许多其他表中用作外键。我想更改索引的数据类型(从有符号整数更改为无符号整数),最好的方法是什么?

我尝试更改索引字段上的数据类型,但失败了,因为它被用作其他表的外键。我尝试更改其中一个外键的数据类型,但失败了,因为它与索引的数据类型不匹配。

我想我可以手动删除所有外键约束,更改数据类型并重新添加约束,但这将是一项很大的工作,因为我有很多表使用此索引作为外键。在进行更改时,是否有方法暂时关闭外键约束?另外,是否有方法获取引用索引作为外键的所有字段的列表?

更新: 我尝试在关闭外键检查后修改一个外键,但它似乎没有关闭检查:

SET foreign_key_checks = 0;

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

以下是错误:

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

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;

以及包含指向它的外键的表:

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;

5 回复 | 直到 17 年前

yivi SilverLink 7 年前

这是我对这篇文章的一点贡献。感谢Daniel Schneller的启发,并为我提供了解决方案的大部分!

set group_concat_max_len = 2048;
set @table_name = "YourTableName";
set @change = "bigint unsigned";
select distinct table_name,
       column_name,
       constraint_name,
       referenced_table_name,
       referenced_column_name,
       CONCAT(
           GROUP_CONCAT('ALTER TABLE ',table_name,' DROP FOREIGN KEY ',constraint_name SEPARATOR ';'),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` CHANGE `',column_name,'` `',column_name,'` ',@change SEPARATOR ';'),
           ';',
           CONCAT('ALTER TABLE `',@table_name,'` CHANGE `',referenced_column_name,'` `',referenced_column_name,'` ',@change),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` ADD CONSTRAINT `',constraint_name,'` FOREIGN KEY(',column_name,') REFERENCES ',referenced_table_name,'(',referenced_column_name,')' SEPARATOR ';')
       ) as query
from   INFORMATION_SCHEMA.key_column_usage
where  referenced_table_name is not null
   and referenced_column_name is not null
   and referenced_table_name = @table_name
group by referenced_table_name

gerdemb 17 年前

为了回答我自己的问题,我找不到更简单的方法。我最终删除了所有外键约束,更改了字段类型,然后又重新添加了所有外键约束。

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

Daniel Schneller 17 年前

INFORMATION_SCHEMA 数据库:

select distinct table_name, 
       column_name, 
       constraint_name,  
       referenced_table_name, 
       referenced_column_name 
from   key_column_usage 
where  constraint_schema = 'XXX' 
   and referenced_table_name is not null 
   and referenced_column_name is not null;

代替 XXX 使用您的模式名称。这将为您提供一个表和列的列表,这些表和列将其他列引用为外键。

不幸的是,架构更改是非事务性的,因此我担心您确实必须暂时禁用此操作的外部密钥检查。我建议(如果可能的话)在此阶段防止来自任何客户端的连接,以将意外违反约束的风险降至最低。

Piotr Pankowski 16 年前

如果您可以停止数据库,然后尝试将表转储到文本文件,手动更改文件中的列定义并将表导入回。

Powerlord 17 年前

您可以通过键入临时禁用外键

SET foreign_key_checks = 0;

并重新启用它们

SET foreign_key_checks = 1;

编辑

Andrey K. 6 年前

这也是我对这篇文章的一点贡献。感谢Daniel Schneller和Wiktor Jarka的启发,并为我提供了解决方案的大部分! 此解决方案修复了数据库和自动增量问题。

SET group_concat_max_len = 2048;
SET @database_name = "my_database";
SET @table_name = "my_table";
SET @change = "tinyint unsigned";
SELECT DISTINCT 
       `table_name`,
       `column_name`,
       `constraint_name`,
       `referenced_table_name`,
       `referenced_column_name`,
       CONCAT(
           GROUP_CONCAT('ALTER TABLE `',table_name,'` DROP FOREIGN KEY `',constraint_name, '`' SEPARATOR ';'),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` CHANGE `',column_name,'` `',column_name,'` ',@change SEPARATOR ';'),
           ';',
           CONCAT('ALTER TABLE `',@table_name,'` CHANGE `',referenced_column_name,'` `',referenced_column_name,'` ',@change, ' NOT NULL AUTO_INCREMENT'),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` ADD CONSTRAINT `',constraint_name,'` FOREIGN KEY(',column_name,') REFERENCES ',referenced_table_name,'(',referenced_column_name,')' SEPARATOR ';'), 
           ';'
       ) AS query
FROM   `information_schema`.`key_column_usage`
WHERE  `referenced_table_name` IS NOT NULL
   AND `referenced_column_name` IS NOT NULL
   AND `constraint_schema` = @database_name
   AND `referenced_table_name` = @table_name
GROUP BY `referenced_table_name`

Christophe L. 5 年前

组concat是一个坏主意,因为您不能控制输出的长度
其他答案不处理空值和默认值,因此可能会弄乱数据库

SET @table_schema = "database";
SET @table_name = "table";
SET @column_name = "column";
SET @new_type = "bigint unsigned";


SELECT CONCAT('ALTER TABLE `', table_schema,'`.`', table_name, '` DROP FOREIGN KEY `', constraint_name,  '`;' ) AS drop_fk
FROM information_schema.key_column_usage
WHERE referenced_table_schema =  @table_schema AND referenced_table_name = @table_name and referenced_column_name = @column_name;


SELECT CONCAT('ALTER TABLE `', kcu.table_schema,'`.`', kcu.table_name,'` MODIFY `', kcu.column_name, '` ',
@new_type, 
if(c.is_nullable = 'NO', ' NOT NULL', ' NULL'),
if(c.column_default is not null, CONCAT(' DEFAULT ', QUOTE(c.column_default)), ''),
if(c.extra <> '', CONCAT(' ', c.extra), ''),
if(c.column_comment <> '', CONCAT(' COMMENT ', QUOTE(c.column_comment), ''), ''),
';') AS modify
FROM information_schema.key_column_usage AS kcu
JOIN information_schema.columns AS c ON c.table_schema=kcu.table_schema AND c.table_name=kcu.table_name AND c.column_name=kcu.column_name
WHERE kcu.referenced_table_schema = @table_schema AND kcu.referenced_table_name = @table_name AND kcu.referenced_column_name = @column_name;


SELECT CONCAT('ALTER TABLE `', table_schema,'`.`',table_name,'` ADD CONSTRAINT `',constraint_name ,'` FOREIGN KEY (`', column_name, '`) REFERENCES `',
referenced_table_schema ,'`.`', referenced_table_name , '` (`', referenced_column_name,'`);') AS create_fk
FROM information_schema.key_column_usage
WHERE referenced_table_schema = @table_schema AND referenced_table_name = @table_name AND referenced_column_name = @column_name;