mysql - 向 MySQL 表添加外键错误 : ERROR 1215 (HY000): Cannot add foreign key constraint

Question

有人能解释一下当我尝试向表添加外键约束时 MySQL 出错的原因吗？

这是我的表格:

mysql> desc workflow_stakeholder_sectors;
+----------------+---------+------+-----+---------+----------------+
| Field          | Type    | Null | Key | Default | Extra          |
+----------------+---------+------+-----+---------+----------------+
| id             | int(11) | NO   | PRI | NULL    | auto_increment |
| stakeholder_id | int(11) | NO   |     | NULL    |                |
| sector_id      | int(11) | NO   |     | NULL    |                |
+----------------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc workflow_stakeholder;
+---------------------------------+--------------+------+-----+---------+----------------+
| Field                           | Type         | Null | Key | Default | Extra          |
+---------------------------------+--------------+------+-----+---------+----------------+
| id                              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name                            | varchar(255) | YES  |     | NULL    |                |
| stakeholder_register            | tinyint(1)   | NO   |     | NULL    |                |
| create_date                     | datetime     | YES  |     | NULL    |                |
| edit_date                       | datetime     | YES  |     | NULL    |                |
| country_id                      | int(11)      | NO   | MUL | NULL    |                |
| formal_relationship_document_id | int(11)      | YES  | MUL | NULL    |                |
| sector_id                       | int(11)      | YES  | MUL | NULL    |                |
| type_id                         | int(11)      | YES  | MUL | NULL    |                |
| vetting_document_id             | int(11)      | YES  | MUL | NULL    |                |
| approval                        | varchar(255) | YES  |     | NULL    |                |
| approved_by_id                  | int(11)      | YES  | MUL | NULL    |                |
| filled_by_id                    | int(11)      | YES  | MUL | NULL    |                |
+---------------------------------+--------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

我正在使用 macOS Sierra 版本 10.12.4 和 mysql Ver 14.14 Distrib 5.7.17，用于使用 EditLine 包装器的 macos10.12 (x86_64)

我使用下面的mysql语句来添加外键:

ALTER TABLE `workflow_stakeholder_sectors` ADD CONSTRAINT 
`workflow_stak_stakeholder_id_b87707c1_fk_workflow_stakeholder_id` 
FOREIGN KEY (`stakeholder_id`) REFERENCES `workflow_stakeholder` (`id`);

我得到的错误是:

ERROR 1215 (HY000): Cannot add foreign key constraint

Answer 1

错误原因的可能可能性是:

• 一个或两个表是 MyISAM 或其他一些存储引擎，而不是 InnoDB。

  感谢您至少为您的表发布了 DESC 输出，但是 DESC 没有显示存储引擎。 SHOW CREATE TABLE 显示该信息。

• workflow_stakeholder_sectors 中存在一些引用 workflow_stakeholder 中缺失的 stakeholder_id 值的行。尝试以下查询:

  SELECT COUNT(*) FROM workflow_stakeholder_sectors wss 
  LEFT OUTER JOIN workflow_stakeholder ws
    ON wss.stakeholder_id = ws.id
  WHERE ws.id IS NULL