在现有 MYSQL 数据库中使用 Alter Table 添加外键时出现问题 - 无法添加！帮助！
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5672472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Problem adding Foreign Key using Alter Table with existing MYSQL Database - can't add it! Help!
提问by egervari
I have a production database where I have renamed several column's that are foreign keys. Obviously mysql makes this a real pain to do in my experience.
我有一个生产数据库，我在其中重命名了几个作为外键的列。显然，根据我的经验，mysql 使这变得非常痛苦。
My solution was to drop all the indexes and foreign keys, rename the id columns, and then re-add the indexes and foreign keys.
我的解决方案是删除所有索引和外键，重命名 id 列，然后重新添加索引和外键。
This works great on mysql 5.1 on windows for the development database.
这在用于开发数据库的 Windows 上的 mysql 5.1 上非常有效。
I went to run my migration script on my debian server, which is also using mysql 5.1, and it gives the following error:
我去我的 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)
There are no values in this table that would conflict with the foreign key I am trying to add. The database hasn't changed. The foreign key DID exist before... so the data is fine. Let's not mention that I took the SAME database that I have on the server and it migrates fine on Windows. But these same foreign key migrations are not taking on Debian.
此表中没有与我尝试添加的外键冲突的值。数据库没有改变。外键 DID 之前存在...所以数据很好。更不用说我使用了服务器上的 SAME 数据库，它在 Windows 上迁移得很好。但是这些相同的外键迁移并没有出现在 Debian 上。
The columns are using the same type - BIGINT (20)
列使用相同的类型 - BIGINT (20)
The names do in fact exist in their respective tables.
这些名称实际上存在于它们各自的表中。
The tables are innodb. They already have foreign keys in other columns as it is. This is not a new database.
表是innodb。他们已经在其他列中拥有外键。这不是一个新的数据库。
I cannot drop tables because this is a production database.
我不能删除表，因为这是一个生产数据库。
The tables "as is" in my database:
我的数据库中的表“原样”：
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
And
和
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
Here is the innodb status:
这是 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
If I try and drop the index from 'company_to_module', I get this error:
如果我尝试从“company_to_module”中删除索引，则会收到此错误：
#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150)
Here are my innodb variables:
这是我的 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 |
+---------------------------------+------------------------+
I also want to add that while I was playing with adding the foreign keys, mysql corrupted my database and destroyed it. I had to reload from a backup to try again.
我还想补充一点，当我在添加外键时，mysql 损坏了我的数据库并破坏了它。我不得不从备份重新加载才能重试。
Help? :/
帮助？：/
采纳答案by egervari
I have simply applied the refactorings using Windows and then reimported the database into Debian - it works.
我只是使用 Windows 应用了重构，然后将数据库重新导入到 Debian 中 - 它可以工作。
I think it's safe to say that something was messed up on the Debian server, or with the linux version of Mysql - perhaps a bug in 5.1 build?
我认为可以肯定地说 Debian 服务器或 linux 版本的 Mysql 出现了一些问题——也许是 5.1 版本中的一个错误？
Anyway, I have also upgraded the ram on the server from 1gb to 2gb, and these problems have gone away.
无论如何，我也将服务器上的ram从1gb升级到2gb，这些问题都消失了。
I think MySQL maybe just didn't have enough ram to complete the operation. If that was the case (and it seems to be), I think MySQL should have simply said so rather than spitting out these errors - making me and everyone here think it was a syntax or a schema-related problem.
我认为 MySQL 可能只是没有足够的 ram 来完成操作。如果是这种情况（而且似乎确实如此），我认为 MySQL 应该简单地说出来，而不是吐出这些错误——让我和这里的每个人都认为这是一个语法或与模式相关的问题。
Anyway, thanks for those that tried to help. At least it helped me to isolate all the things it couldn't have been.
无论如何，感谢那些试图提供帮助的人。至少它帮助我隔离了所有它不可能存在的东西。
回答by ypercube??
Are both tables InnoDB type?
两个表都是 InnoDB 类型吗？
Does the company table have an index on company_id ?
公司表在 company_id 上有索引吗？
I guess that your table is MyISAM (the default if you haven't changed the config) and you can't create foreign key constraints in MyISAM. See the description of the CREATE TABLE for yout two tables.
我猜您的表是 MyISAM（如果您没有更改配置，则为默认值）并且您无法在 MyISAM 中创建外键约束。请参阅您两个表的 CREATE TABLE 的描述。
If both tables are empty, drop them and re-create them, choosing InnoDB as engine. You could also add the FOREIGN KEY constraints in the tables creation script(s).
如果两个表都是空的，删除它们并重新创建它们，选择 InnoDB 作为引擎。您还可以在表创建脚本中添加 FOREIGN KEY 约束。
From MySQL Reference Manual:
来自 MySQL参考手册：
Foreign keys definitions are subject to the following conditions:
Both tables must be InnoDBtables and they must not be TEMPORARY tables.
Corresponding columnsin the foreign key and the referenced key must have similar internal data typesinside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.
InnoDB requires indexes on foreign keysand referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign keybecause indexes on those columns must always include a prefix length.
If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.
外键定义受以下条件的约束：
两个表都必须是 InnoDB表并且它们不能是 TEMPORARY 表。
外键和引用键中的对应列必须在 InnoDB内部具有相似的内部数据类型，以便它们可以在不进行类型转换的情况下进行比较。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。
InnoDB 需要外键和引用键的索引，以便外键检查可以快速并且不需要表扫描。在引用表中，必须有一个索引，其中外键列以相同的顺序列为第一列。如果引用表不存在，则会在引用表上自动创建这样的索引。（这与某些旧版本形成对比，在这些旧版本中必须显式创建索引，否则外键约束的创建将失败。） index_name，如果给定，则如前所述使用。
InnoDB 允许外键引用任何索引列或列组。但是，在被引用的表中，必须有一个索引，其中被引用的列以相同的顺序列为第一列。
不支持外键列的索引前缀。这样做的一个后果是BLOB 和 TEXT 列不能包含在外键中，因为这些列上的索引必须始终包含前缀长度。
如果给出了 CONSTRAINT 符号子句，则符号值在数据库中必须是唯一的。如果没有给出子句，InnoDB 会自动创建名称。
@egervari: What happens if you run this:
@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 ;
And if you run:
如果你运行：
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;
回答by squawknull
Ensure that company_to_module.company_id and company.company_id are the EXACT same datatype. I had this happen when the primary key was setup as an UNSIGNED INT but the foreign key field was just an INT. Adding UNSIGNED to the datatype fixed the problem.
确保 company_to_module.company_id 和 company.company_id 是完全相同的数据类型。当主键设置为 UNSIGNED INT 但外键字段只是一个 INT 时，我发生了这种情况。添加 UNSIGNED 到数据类型解决了这个问题。
回答by nickgrim
Since it doesn't seem to be anything syntax-related, my best guess would be that you're running out of space for creating InnoDB tables.
由于它似乎与语法无关，我最好的猜测是您的空间不足，无法创建 InnoDB 表。
EDIT: Can you paste your InnoDB configuration:
编辑：你能粘贴你的 InnoDB 配置吗：
SHOW VARIABLES LIKE "inno%";
回答by titanoboa
Since trying to create a copy of company_to_modulemanually gives you the same error, you should carefully check the fk constraint already present in company_to_module. Is it still valid, or did you modify the table module?
由于尝试company_to_module手动创建 的副本会给您带来相同的错误，因此您应该仔细检查company_to_module. 它仍然有效，还是您修改了表格module？
From the MySQL-Docs:
来自MySQL-Docs：
1005 (ER_CANT_CREATE_TABLE) Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
1005 (ER_CANT_CREATE_TABLE) 无法创建表。如果错误消息涉及错误 150，则表创建失败，因为外键约束未正确形成。
回答by Devart
@egervari You wrote - My solution was to drop all the indexes and foreign keys, rename the id columns, and then re-add the indexes and foreign keys.
@egervari 你写的 - 我的解决方案是删除所有索引和外键，重命名 id 列，然后重新添加索引和外键。
Agree with you. But it might be that something went wrong. I reproduced the error, and (in my case) fixed it.
同意您。但也可能是出了什么问题。我重现了错误，并且（就我而言）修复了它。
I'd suggest you to run OPTIMIZE TABLE command for table where column was renamed. Documentation says - For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.
我建议您对重命名列的表运行 OPTIMIZE TABLE 命令。文档说 -对于 InnoDB 表，OPTIMIZE TABLE 映射到 ALTER TABLE，它重建表以更新索引统计信息并释放聚集索引中未使用的空间。
One more solution:
另一种解决方案：
Drop unique key in the referenced table (key that is used by foreign key, in your case it is a primary key). Then add new foreign key and recreate droped unique key.
删除引用表中的唯一键（外键使用的键，在您的情况下它是主键）。然后添加新的外键并重新创建删除的唯一键。
One more solution:
另一种解决方案：
Try to add and drop new column to the referenced table, then try to create your foreign key.
尝试向引用表添加和删除新列，然后尝试创建外键。
ALTER TABLE company ADD COLUMN column1 VARCHAR(255) DEFAULT NULL;
ALTER TABLE company DROP COLUMN column1;
