MySQL 无法创建外键约束

Question

我在创建一个MySQL数据库中现有表的外键时遇到了一些问题。

我有一个名为exp的表：

+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| EID         | varchar(45)      | NO   | PRI | NULL    |       |
| Comment     | text             | YES  |     | NULL    |       |
| Initials    | varchar(255)     | NO   |     | NULL    |       |
| ExpDate     | date             | NO   |     | NULL    |       |
| InsertDate  | date             | NO   |     | NULL    |       |
| inserted_by | int(11) unsigned | YES  | MUL | NULL    |       |
+-------------+------------------+------+-----+---------+-------+

我希望创建一个新的表格，名为sample_df，它将引用此内容并使用以下代码：

CREATE TABLE sample_df (
    df_id         mediumint(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sample_type   mediumint(5) UNSIGNED          NOT NULL,
    df_10         boolean                        NOT NULL,
    df_100        boolean                        NOT NULL,
    df_1000       boolean                        NOT NULL,
    df_above_1000 boolean                        NOT NULL,
    target        int(11) UNSIGNED               NOT NULL,
    assay         mediumint(5) UNSIGNED ZEROFILL NOT NULL,
    insert_date   timestamp                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    inserted_by   int(11) UNSIGNED               NOT NULL,
    initials      varchar(255),
    experiment    varchar(45),
    CONSTRAINT FOREIGN KEY (inserted_by) REFERENCES user (iduser),
    CONSTRAINT FOREIGN KEY (target) REFERENCES protein (PID),
    CONSTRAINT FOREIGN KEY (sample_type) REFERENCES sample_type (ID),
    CONSTRAINT FOREIGN KEY (assay) REFERENCES assays (AID),
    CONSTRAINT FOREIGN KEY (experiment) REFERENCES exp (EID)
);

但是我遇到了以下错误：

ERROR 1215 (HY000)：无法添加外键约束

为了获取更多信息，我执行了以下操作：

SHOW ENGINE INNODB STATUS\G

我得到的信息：

FOREIGN KEY (experiment) REFERENCES exp (EID) 
): 
Cannot find an index in the referenced table where the 
referenced columns appear as the first columns, or column types 
in the table and the referenced table do not match for constraint.

对我来说，列类型似乎匹配，因为它们都是varchar(45)。(我还尝试将experiment列设置为not null，但这并没有解决问题)。所以我猜问题一定是：

在参考表中找不到索引，其中被参考的列出现在第一列。

但我不太确定这意味着什么，或者如何检查/修复它。有人有什么建议吗？而且什么是第一列的含义？

- numfar

---

在任何问题或答案中，我都无法找到这个错误可能是由于字符集的差异引起的。谢谢。干杯！ - proprius

另外，要仔细检查你的列排序规则。你的表可能是utf8mb4类型的，但该列可能有不同的排序规则！ - Watson

在我的情况下是@Watson字符集，但你得到了我的赞赏，因为让我思考了一下。 - user2910265

Answer 1

仅仅是为了提供可能的原因，我想加入一些内容。当引用表中的列与被引用表中的列“类型”相同时但未签名相同时，我遇到了这个问题。

在我的情况下，被引用的表列是TINYINT UNSIGNED，而我的引用表列是TINYINT SIGNED。将两个列对齐解决了这个问题。

- Austen Hoogen

---

@austen-hoogen提出的解决方案已经解决了我的问题。但由于声望分数较低，我无法为他的解决方案投票和评论。 在我的情况下，主键是整数、自动递增和无符号的。但是外键（引用表的列）是有符号类型。我将其更改为无符号类型，并成功创建了关系。 - Bal Singh

是的，我也遇到了同样的问题。我的主键是BIGINT（20），而在另一个表中引用的键是INT（10）无符号。我们需要确保它们全部匹配。即BIGINT（20），在引用的表中也应该是BIGINT（20）。 - Manjunath Reddy

谢谢！这个解决方案也解决了我的问题！我在第一个表中使用了 int，而在第二个表中使用了 bigint。 - Aleksej_Shherbak

Answer 2

如果参考表和当前表的字符集不相同，也会出现此错误。

- Pratik Singhal

---

在参考表格中将字符集设置为latin后，我在3小时后开始呼吸。 - Abhishek Kamal

这感觉像是疯狂，居然会变成问题，但我想老表不能一次全部升级到utf-8之类的。我的新表默认为latin1。 - Chris Barry

我遇到了同样的问题，尽管所有表都设置为“默认字符集”，但我不得不明确地设置utf8才能使其正常工作。 - zolastro

Answer 3

根据 http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html ，MySQL要求在外键和被引用键上创建索引，以便外键检查可以快速进行，而不需要表扫描。在引用表中，必须有一个索引，其中外键列按相同顺序列出为第一列。

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

因此，如果被引用表中的索引存在且由几个列组成，并且所需列不是第一个，则会发生错误。

我们错误的原因是违反了以下规则：

外键和被引用键中的对应列必须具有类似的数据类型。整数类型的大小和符号必须相同。字符串类型的长度不需要相同。对于非二进制（字符）字符串列，字符集合校对必须相同。

- Anton

---

这很有帮助，因为我的复合主键中，我的外键被定义为第二个（即 CONSTRAINT pk_dataAssetId PRIMARY KEY (assetId, fk_key)）。我必须将其更改为主键约束中的第一列（即 CONSTRAINT pk_dataAssetId PRIMARY KEY (fk_key, assetId)）。 - Sas

Answer 4

如@Anton提到的那样，这可能是由于不同的数据类型引起的。 在我的情况下，我使用了主键BIGINT(20)，并尝试使用INT(10)设置外键。

- gorodezkiy

---

谢谢！我遇到了类似的问题，我使用了INT(11)和INT(11)，但一个是主键并且无符号，而另一个是有符号的！ - Michael Scott Asato Cuthbert

Answer 5

我的问题是由于被引用的表和要创建的表之间的排序规则不匹配，因此我必须显式设置我要引用的键的排序规则。

• 首先，我在被引用的表上运行了一个查询来获取它的排序规则

show table STATUS like '<table_name_here>';

• 然后我复制了排序类型，并在创建查询中明确说明了employee_id的排序类型。在我的情况下，它是utf8_general_ci

注意：以上是已翻译内容，仅供参考。

CREATE TABLE dbo.sample_db
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_id INT SIGNED NOT NULL,
  employee_id varchar(45) COLLATE utf8_general_ci NOT NULL,
  event_date_time DATETIME,
  CONSTRAINT sample_db_event_event_id_fk FOREIGN KEY (event_id) REFERENCES event (event_id),
  CONSTRAINT sample_db_employee_employee_id_fk FOREIGN KEY (employee_id) REFERENCES employee (employee_id)
);

- user11891461

Answer 6

对我来说，问题只是数据库的字符集和校对规则。我将其改为 utf8_unicode_ci 就可以正常工作了。

- Diego Herrera

---

你能否提供一个检查当前字符集/排序规则的示例？ - thelr

@他们的 alter schema my_database default character set utf8 default collate utf8_general_ci; - g.delgado

Answer 7

在我的情况下，结果表明引用的列没有声明为主键或唯一。 https://dev59.com/SGMl5IYBdhLWcg3wZWSD#18435114

- Nick Manning

Answer 8

在我的情况下，这是由于ENGINE和COLLATE不兼容导致的。一旦我添加了ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci，它就可以工作了。

CREATE TABLE `some_table` (
      `id` varchar(36) NOT NULL,
      `col_id` varchar(36) NOT NULL,
      
      PRIMARY KEY (`id`),
      CONSTRAINT `FK_some_table_cols_col_id` FOREIGN KEY (`col_id`) REFERENCES `ref_table` (`id`) ON DELETE CASCADE,
      
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

- Андрей Пухкало

Answer 9

在我的情况下，使用整数作为id创建了一个表，引用表默认创建了一个使用大整数的外键。

这在我的Rails应用程序中造成了巨大的噩梦，因为迁移失败，但实际上字段已经在数据库中创建，所以它们显示在数据库中，但不在Rails应用程序的模式中。

- helenatxu

Answer 10

主键的确切顺序也需要匹配，中间不能有额外的列。我曾经设置过一个主键，其中列的顺序实际上是匹配的，但问题在于主键中有一个额外的列，它不是引用表的外键的一部分。例如）表2，列（a，b，c）->表1，列（a，b，d，c）-- 这会失败。我不得不重新排列主键列，以便它们不仅按相同方式排序，而且中间没有额外的列：例如）表2，列（a，b，c）->表1，列（a，b，c，d）-- 这将成功。

- Nelson