問題描述

我正在嘗試在 MySQL 中創建一個帶有兩個外鍵的表，該表引用了其他 2 個表中的主鍵，但是我收到了 errno: 150 錯誤并且它不會創建表.

I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table.

這是所有 3 個表的 SQL:

Here is the SQL for all 3 tables:

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;

任何幫助將不勝感激.

推薦答案

我在使用 ALTER TABLE ADD FOREIGN KEY 時遇到了同樣的問題.

I had the same problem with ALTER TABLE ADD FOREIGN KEY.

一個小時后，我發現必須滿足這些條件才不會出現錯誤 150:

After an hour, I found that these conditions must be satisfied to not get error 150:

1. 在定義外鍵以引用它之前，父表必須存在.您必須按正確的順序定義表:首先是父表，然后是子表.如果兩個表相互引用，則必須創建一個沒有 FK 約束的表，然后創建第二個表，然后使用 ALTER TABLE 將 FK 約束添加到第一個表.

1. The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with ALTER TABLE.

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

The two tables must both support foreign key constraints, i.e. ENGINE=InnoDB. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.

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

The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is PRIMARY KEY or UNIQUE KEY.

FK 定義必須以與 PK 定義相同的順序引用 PK 列.例如，如果 FK REFERENCES Parent(a,b,c) 那么 Parent 的 PK 不得按 (a,c,b) 的順序在列上定義.

The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK REFERENCES Parent(a,b,c) then the Parent's PK must not be defined on columns in order (a,c,b).

父表中的 PK 列必須與子表中的 FK 列具有相同的數據類型.例如，如果 Parent 表中的 PK 列是 UNSIGNED，請確保為 Child 表字段中的相應列定義 UNSIGNED.

The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is UNSIGNED, be sure to define UNSIGNED for the corresponding column in the Child table field.

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

Exception: length of strings may be different. For example, VARCHAR(10) can reference VARCHAR(20) or vice versa.

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

Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).

如果子表中已有數據，則 FK 列中的每個值都必須與父表 PK 列中的值匹配.使用以下查詢進行檢查:

If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:

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

這必須返回零 (0) 個不匹配的值.顯然，這個查詢是一個通用的例子；您必須替換您的表名和列名.

This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.

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

Neither the Parent table nor the Child table can be a TEMPORARY table.

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

Neither the Parent table nor the Child table can be a PARTITIONED table.

如果您使用 ON DELETE SET NULL 選項聲明 FK，則 FK 列必須可以為空.

If you declare a FK with the ON DELETE SET NULL option, then the FK column(s) must be nullable.

如果為外鍵聲明約束名稱，則約束名稱在整個架構中必須是唯一的，而不僅僅是在定義約束的表中.兩個表可能沒有自己的同名約束.

If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.

如果其他表中有任何其他 FK 指向您嘗試為其創建新 FK 的同一字段，并且它們的格式不正確(即不同的排序規則)，則需要首先使它們保持一致.這可能是過去更改的結果，其中 SET FOREIGN_KEY_CHECKS = 0; 與錯誤定義的不一致關系一起使用.有關如何識別這些 FK 問題的說明，請參閱下方 @andrewdotn 的回答.

If there are any other FK's in other tables pointing at the same field you are attempting to create the new FK for, and they are malformed (i.e. different collation), they will need to be made consistent first. This may be a result of past changes where SET FOREIGN_KEY_CHECKS = 0; was utilized with an inconsistent relationship defined by mistake. See @andrewdotn's answer below for instructions on how to identify these problem FK's.

希望這會有所幫助.

這篇關于MySQL 創建帶有外鍵的表給出 errno: 150的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！