使用ALTER TABLE在现有的MYSQL数据库中添加外键时出现问题 - 无法添加它！求助！

Question

我有一个生产数据库，其中我已经重命名了几个作为外键的列。显然，在我的经验中，mysql做这件事情真是一件很麻烦的事情。

我的解决方案是删除所有索引和外键，重命名id列，然后重新添加索引和外键。

在开发数据库上，这对于运行在Windows上的mysql 5.1非常有效。

我想在我的Debian服务器上运行迁移脚本，该服务器也使用mysql 5.1，但是它会出现以下错误：

mysql> ALTER TABLE `company_to_module`
    -> ADD CONSTRAINT `FK82977604FE40A062` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1005 (HY000): Can't create table 'jobprep_production.#sql-44a5_76' (errno: 150)

在这个表中没有与我正在尝试添加的外键冲突的值。数据库并没有更改。外键之前确实存在，所以数据没问题。请注意，我使用了与服务器上相同的数据库，它可以在Windows上成功迁移。但是，在Debian上这些外键迁移就不起作用。

列使用相同的类型 - BIGINT(20)

名称确实存在于它们各自的表中。

表是innodb。他们已经有其他列的外键。这不是一个新的数据库。

我不能删除表，因为这是一个生产数据库。

我的数据库中的表：

 CREATE TABLE `company_to_module` (
  `company_id` bigint(20) NOT NULL,
  `module_id` bigint(20) NOT NULL,
  KEY `FK8297760442C8F876` (`module_id`),
  KEY `FK82977604FE40A062` (`company_id`) USING BTREE,
  CONSTRAINT `FK8297760442C8F876` FOREIGN KEY (`module_id`) REFERENCES `module` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

并且

Create Table: CREATE TABLE `company` (
  `company_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `postal_code` varchar(255) DEFAULT NULL,
  `province_id` bigint(20) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,
  `is_enabled` bit(1) DEFAULT NULL,
  `director_id` bigint(20) DEFAULT NULL,
  `homepage_viewable` bit(1) NOT NULL DEFAULT b'1',
  `courses_created` int(10) NOT NULL DEFAULT '0',
  `header_background` varchar(25) DEFAULT '#172636',
  `display_name` varchar(25) DEFAULT '#ffffff',
  `tab_background` varchar(25) DEFAULT '#284767',
  `tab_text` varchar(25) DEFAULT '#ffffff',
  `hover_tab_background` varchar(25) DEFAULT '#284767',
  `hover_tab_text` varchar(25) DEFAULT '#f2e0bd',
  `selected_tab_background` varchar(25) DEFAULT '#f5f5f5',
  `selected_tab_text` varchar(25) DEFAULT '#172636',
  `hover_table_row_background` varchar(25) DEFAULT '#c0d2e4',
  `link` varchar(25) DEFAULT '#4e6c92',
  PRIMARY KEY (`company_id`),
  KEY `FK61AE555A71DF3E03` (`province_id`),
  KEY `FK61AE555AAC50C977` (`director_id`),
  CONSTRAINT `company_ibfk_1` FOREIGN KEY (`director_id`) REFERENCES `user_account` (`user_account_id`),
  CONSTRAINT `FK61AE555A71DF3E03` FOREIGN KEY (`province_id`) REFERENCES `province` (`province_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8

这是InnoDB状态：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110415  3:14:34 Error in foreign key constraint of table jobprep_production/#sql-44a5_1bc:
 FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT:
Cannot resolve column name close to:
) ON DELETE RESTRICT ON UPDATE RESTRICT

如果我试图从'company_to_module'中删除索引，会出现以下错误：

#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150) 

以下是我的InnoDB变量：

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 1048576                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 8388608                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_io_threads          | 4                      |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 1048576                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_open_files               | 300                    |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_stats_on_metadata        | ON                     |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 20                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
+---------------------------------+------------------------+

我还想补充一点，当我尝试添加外键时，MySQL 损坏了我的数据库并将其破坏了。我不得不从备份中重新加载以再次尝试。

需要帮助吗？ :/

- egervari

---

“company”表上是否有“company_id”索引？ - ypercubeᵀᴹ

我猜你的表是 MyISAM（如果你没有更改配置，则为默认值），而且我认为你不能在 MyISAM 中创建外键约束。请参阅 CREATE TABLE company_to_module 的描述。 - ypercubeᵀᴹ

如果两个表都为空，则删除它们并重新创建它们，选择 InnoDB 作为引擎。您还可以在表创建脚本中添加 FOREIGN KEY 约束。 - ypercubeᵀᴹ

@Ypercube：感谢您的建议。两个表都不为空——这是一个生产数据库。我必须使用alter table。所有表都是innodb。实际上，这些表上已经存在外键——这不像是第一次尝试这样做。这个数据库有40个表，所有表中有大量的外键。所以我不认为这是其中任何一个问题:(是的，公司ID是“company_id”。我确保了这一点。尽管没有外键，但应用程序实际上可以与数据库交互……但我仍然想添加它们。 - egervari

@mu 太短了：jobprep_production 是数据库名称。但我完全不确定为什么会出现这个错误。 - egervari

Answer 1

这两个表都是InnoDB类型吗？

公司表上有company_id的索引吗？

我猜你的表是MyISAM（如果你没有更改配置，则为默认值），在MyISAM中无法创建外键约束。请参阅yout两个表的CREATE TABLE的描述。

如果两个表都为空，请删除它们并重新创建它们，选择InnoDB作为引擎。您也可以在表创建脚本中添加FOREIGN KEY约束。

---

来自 MySQL 参考手册：

外键定义受以下条件限制：

• 两个表必须是InnoDB表，且不能是TEMPORARY表。

• 外键和所引用的键中的对应列在InnoDB内部数据类型上必须相似，以便它们可以进行比较而无需进行类型转换。整数类型的大小和符号必须相同。字符串类型的长度不需要相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。

• InnoDB需要在外键和所引用的键上建立索引，以便外键检查可以快速执行，而不需要进行表扫描。在引用表中，必须有一个索引，其中外键列按照相同顺序列出作为第一列。如果该索引不存在，则会自动在引用表上创建此类索引。这与一些旧版本不同，在那些版本中必须显式地创建索引，否则创建外键约束将失败。如果给出了index_name，则像之前描述的那样使用它。

• InnoDB允许外键引用任何索引列或列组。但是，在所引用的表中，必须有一个索引，其中所引用的列按照相同顺序列出作为第一列。

• 不支持外键列上的索引前缀。这样做的一个后果是，BLOB和TEXT列不能包含在外键中，因为这些列上的索引必须始终包含前缀长度。

• 如果给出了CONSTRAINT symbol子句，则symbol值必须在数据库中是唯一的。如果没有给出该子句，则InnoDB会自动创建名称。

---

@egervari：如果你运行这个会发生什么：

CREATE TABLE `test` (
  `company_id` bigint(20) NOT NULL,
  `module_id` bigint(20) NOT NULL,

  KEY  (`module_id`),
  KEY  (`company_id`),

  CONSTRAINT `test_fk_module`
    FOREIGN KEY (`module_id`)
    REFERENCES `module` (`module_id`),

  CONSTRAINT `test_fk_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `company` (`company_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

---

如果你运行：

ALTER TABLE `company_to_module`
  ADD CONSTRAINT `company_to_module_fk_company` 
    FOREIGN KEY (`company_id`)
    REFERENCES `company` (`company_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

- ypercubeᵀᴹ

---

是的，我正在做所有这些事情。表格是InnoDB。ID已正确命名。数据是正确的。类型也是正确的。问题是：假设我违反了这些规则...为什么这些迁移在Windows上使用完全相同的数据库可以工作？这是最令人困惑的事情。 - egervari

低内存可能是这个错误的原因吗？这只是一个云服务器，“free”通常显示我有1GB中的29-9兆可用。只是好奇 :/ 不过很多都用于缓冲，所以我不确定这意味着什么。 - egervari

@egervari：当您运行上述的CREATE TABLE时，是否会收到类似的消息？ - ypercubeᵀᴹ

如果你运行 ALTER TABLE 命令呢？ - ypercubeᵀᴹ

在Debian的MySQL上它没起作用（显然我之前试过...这就是我尝试做的事情），但你的alter table语句在Windows数据库上能够工作（使用相同的.sql文件），就像我的一样。 - egervari

Answer 2

确保company_to_module.company_id和company.company_id具有完全相同的数据类型。当主键设置为UNSIGNED INT而外键字段仅为INT时，我遇到了这种情况。将UNSIGNED添加到数据类型中即可解决问题。

- squawknull

---

检查一下也值得尝试。我的所有ID都是BIGINT(20) :( - egervari

我只是要粘贴我的表定义。 - egervari

对我有用 - 我有一个未签名的整数和另一个整数，我正在尝试链接它们。谢谢！ - STT LCU

Answer 3

我只是在Windows上应用了重构，然后将数据库重新导入到Debian中 - 它正常工作。

我认为可以肯定地说，在Debian服务器上出现了一些问题，或者是与Linux版本的MySQL有关 - 也许是5.1版本中的一个错误？

无论如何，我还将服务器的内存从1GB升级到2GB，这些问题都解决了。

我认为MySQL可能只是没有足够的内存来完成操作。如果是这种情况（看起来是这样），我认为MySQL应该直接说明，而不是输出这些错误信息 - 让我和这里的每个人都以为是语法或模式相关的问题。

总之，感谢那些尝试帮助的人。至少它帮助我排除了所有可能性。

- egervari

Answer 4

由于似乎没有任何语法相关的问题，我最好的猜测是您正在创建InnoDB表时空间不足。

编辑：您能否粘贴您的InnoDB配置：

SHOW VARIABLES LIKE "inno%";

- nickgrim

---

“innodb_data_file_path”在我的my.cnf文件中根本找不到，原因不明。 “innodb_file_per_table”也是如此。我个人没有对my.cnf进行任何更改-这些都是默认设置。这可能是问题吗？默认值真的很低吗？ - egervari

这是ibdata1的统计信息："35651584 Apr 15 03:20 ibdata1"。 - egervari

我把变量粘贴在上面了 ;) - egervari

Answer 5

@egervari 你写道 - 我的解决方案是删除所有索引和外键，重命名id列，然后重新添加索引和外键。

我同意你的做法。但可能出了些问题。我重现了这个错误，并在我的情况下修复了它。

我建议你对重命名列的表运行OPTIMIZE TABLE命令。文档说 - 对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE，该操作重建表以更新索引统计信息并释放聚簇索引中未使用的空间。

---

另一种解决方案：

在被引用的表中删除唯一键（即外键使用的键，在您的情况下是主键）。然后添加新的外键并重新创建已删除的唯一键。

---

另外一种解决方案：

尝试向引用表添加和删除新列，然后尝试创建您的外键。

ALTER TABLE company ADD COLUMN column1 VARCHAR(255) DEFAULT NULL;
ALTER TABLE company DROP COLUMN column1;

- Devart

---

尽管我从未提及过，但我确实对所有事情进行了表格优化。不幸的是，那也没有起作用。 - egervari

我不能删除任何键。这是一个生产数据库。 :/ - egervari

Answer 6

由于手动创建company_to_module的副本会导致相同的错误，因此您应仔细检查company_to_module中已经存在的fk约束。它是否仍然有效，或者您修改了module表？

来自MySQL-Docs：

1005（ER_CANT_CREATE_TABLE）无法创建表。如果错误消息引用错误150，则表格创建失败，因为外键约束未正确形成。

- titanoboa

---

它出错了，因为我在company_to_module的重复表定义中忘记更改外键名称。一旦我意识到这一点，创建表格就可以工作了 - 这意味着创建新表不是问题所在。然而，删除'module_id'索引导致了我上面写的新错误。老实说，我对这种“猜测工作”感到非常厌倦和疲惫，所以我只是将我的数据库导出，在Windows上添加了外键（是的，它们有效！），然后重新将数据库导入Debian。它现在可以工作了。我不知道为什么它在Windows上可以工作而在Linux上不能。跨平台兼容性就这样了。 - egervari