Mysql，创建外键失败

Question

这些列只是 int(10)。外表列是主键。为什么会出现这个错误？

引用表:

mysql> describe civicrm_entity_financial_account;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| entity_table         | varchar(64)      | NO   |     | NULL    |                |
| entity_id            | int(10) unsigned | NO   |     | NULL    |                |
| account_relationship | int(10) unsigned | NO   |     | NULL    |                |
| financial_account_id | int(10) unsigned | NO   |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

引用表:

mysql> describe civicrm_financial_account;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name            | varchar(255)     | NO   |     | NULL    |                |
| account_type_id | int(10) unsigned | NO   |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

SQL命令:

ALTER TABLE `civicrm_entity_financial_account`  ADD CONSTRAINT `FK_civicrm_entity_financial_account_financial_account_id` FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account` (`id`);


ERROR 1215 (HY000): Cannot add foreign key constraint

Answer 1

首先，您要确保表的引擎是 InnoDB，而不是 MyISAM，因为后者不支持外键。如果是这种情况，您可以更换引擎:

ALTER TABLE table_name ENGINE = InnoDB;

如果这不是问题，您可以通过运行来检查特定错误:

SHOW ENGINE INNODB STATUS\G

然后查看最新外键错误部分。

祝你好运!