MySQL 錯誤 150：建立表格時排除外鍵約束故障

Question

我試圖在MySQL 中建立一個帶有兩個外鍵的表，這兩個外鍵引用另外2 個表中的主鍵，但我收到errno: 150 錯誤，並且它不會建立該表。

以下是所有 3 個表的 SQL：

CREATE TABLE role_groups (
  `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
  `name` varchar(20),
  `description` varchar(200),
  PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  `description` varchar(200),
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;

create table role_map (
  `role_map_id` int not null `auto_increment`,
  `role_id` int not null,
  `role_group_id` int not null,
  primary key(`role_map_id`),
  foreign key(`role_id`) references roles(`role_id`),
  foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;

#

Answer 1

必須滿足這些條件，才不會在 ALTER TABLE ADD FOREIGN KEY 時發生錯誤 150：

1. 在定義外鍵來引用它之前，父表必須存在。您必須按正確的順序定義表：首先是父表，然後是子表。如果兩個表相互引用，則必須建立一個沒有 FK 約束的表，然後建立第二個表，然後使用 ALTER TABLE 將 FK 約束新增到第一個表。

2. 兩個表必須都支援外鍵約束，即 ENGINE=InnoDB。其他儲存引擎默默地忽略外鍵定義，因此它們不會傳回錯誤或警告，但不會保存 FK 約束。

3. 父表中引用的列必須是鍵的最左邊的列。如果父項中的鍵是 PRIMARY KEY 或 UNIQUE KEY，則最好。

4. FK 定義必須以與 PK 定義相同的順序引用 PK 欄位。例如，如果 FK REFERENCES Parent(a,b,c)，則不得按 (a,c,b) 的順序在列上定義 Parent 的 PK。 < /p>

5. 父表中的 PK 欄位必須與子表中的 FK 欄位具有相同的資料型別。例如，如果父表中的 PK 列為 UNSIGNED，請務必為子表格欄位中的對應列定義 UNSIGNED。

   例外：字串的長度可能不同。例如，VARCHAR(10) 可以引用 VARCHAR(20)，反之亦然。

6. 任何字串類型的 FK 列都必須與對應的 PK 列具有相同的字元集和排序規則。

7. 如果子表中已有數據，則 FK 列中的每個值都必須與父表 PK 列中的值相符。使用以下查詢檢查這一點：

   SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK 
    WHERE Parent.PK IS NULL;

   這必須傳回零 (0) 個不符合的值。顯然，這個查詢是一個通用的例子；您必須替換表名和列名。

8. 父表和子表都不能是TEMPORARY表。

9. 父表和子表都不能是 PARTITIONED 表。

10. 如果使用 ON DELETE SET NULL 選項宣告 FK，則 FK 欄位必須可為空。

11. 如果為外鍵宣告約束名稱，則該約束名稱在整個模式中必須是唯一的，而不僅僅是在定義該約束的表中唯一。兩個表不能有自己的同名約束。

12. 如果其他表中有任何其他 FK 指向您嘗試為其創建新 FK 的同一字段，並且它們格式錯誤（即不同的排序規則），則需要先使它們保持一致。這可能是由於過去的變更造成的，其中 SET FOREIGN_KEY_CHECKS = 0; 被錯誤地定義為不一致的關係。有關如何識別這些問題 FK 的說明，請參閱下面 @andrewdotn 的回答。