使用现有 MYSQL 数据库的更改表添加外键时出现问题 - 无法添加它！帮助！ - Problem adding Foreign Key using Alter Table with existing MYSQL Database

我有一个生产数据库，我已经重命名了几个列的外键。显然，在我的经验中，mysql使这成为一个真正的痛苦。

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

这个在mysql 5.1在windows上为开发数据库工作得很好。

我在我的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。它们在其他列中已经有了外键。这不是一个新的数据库。

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

我的数据库中的表"as is":

 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破坏了我的数据库并销毁了它。我必须从备份中重新加载才能重试。

帮助吗?:/

两个表都是InnoDB类型吗?

company表在company_id上有索引吗?

我猜你的表是MyISAM(默认如果你没有改变配置)，你不能在MyISAM中创建外键约束。请参见CREATE TABLE对两个表的描述。

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

From MySQL Reference Manual:

外键定义受以下条件约束:

两个表必须是InnoDB表和

对应列键和被引用的键必须具有内部类似的内部数据类型大小整数类型的符号必须是同样的。字符串类型需要的长度不一样。非(字符)字符串列，该字符集和排序必须是相同的 .

InnoDB需要索引键和引用键，以便外键检查可以快速且有效不需要表扫描。在引用表中，必须有索引所在的外键列的第一列同样的顺序。这样的索引是在引用表上创建如果不存在，则自动执行。(这与一些更老的版本，其中索引必须是显式创建的或创建的外键约束将失效。)如果指定了Index_name，则用作前面描述的。

InnoDB允许使用外键引用任何索引列或组的列。然而，在引用的表中，必须有引用列所在的索引的第一列

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

如果约束符号子句为给定，符号值必须为在数据库中唯一。如果条款， 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;

确保company_to_module.company_id和company.company_id是完全相同的数据类型。当主键设置为UNSIGNED INT但外键字段只是INT时，就会发生这种情况。将UNSIGNED添加到数据类型中可以解决此问题。

我只是使用Windows应用了重构，然后将数据库重新导入到Debian中-它可以工作。

我想可以肯定地说，是Debian服务器上的问题，或者是linux版本的Mysql——也许是5.1版本的bug ?

无论如何，我也把服务器上的内存从1gb升级到2gb，这些问题都消失了。

我想MySQL可能只是没有足够的内存来完成操作。如果是这样的话(看起来是这样)，我认为MySQL应该简单地说出来，而不是把这些错误吐出来——这让我和这里的每个人都认为这是语法或模式相关的问题。

无论如何，感谢那些试图帮助我的人。至少它帮助我隔离了所有不可能发生的事情。

由于它似乎没有任何语法相关的东西，我最好的猜测是您正在耗尽创建InnoDB表的空间。

编辑:你可以粘贴你的InnoDB配置:

SHOW VARIABLES LIKE "inno%";

由于尝试手动创建company_to_module的副本会给您相同的错误，因此您应该仔细检查company_to_module中已经存在的fk约束。还是修改了表module ?

From MySQL-Docs:

1005 (ER_CANT_CREATE_TABLE)无法创建表。如果错误消息指向错误150，则表创建失败，因为外键约束没有正确形成。

@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;

使用现有 MYSQL 数据库的更改表添加外键时出现问题 - 无法添加它！帮助！

相关内容

最新更新

热门标签：