MySQL 外键错误 1005 errno 150

MySQL Foreign Key Error 1005 errno 150

提问by IssamTP

I'm doing a small DataBase with MySQL Workbench. I have a main table, called "Immobili", which has a Primary Key composed by four columns: (Comune, Via, Civico, Immobile).

我正在用 MySQL Workbench 做一个小型数据库。我有一个名为“Immobili”的主表，它有一个由四列组成的主键：（Comune、Via、Civico、Immobile）。

Now, I also have three other tables, wich have the same primary key (Comune, Via, Civico, Immobile), but these fields are also referenced to the table Immobili.

现在，我还有其他三个表，它们具有相同的主键（Comune、Via、Civico、Immobile），但这些字段也引用到表 Immobili。

First question: Can I make a Primary Key that is also a Foreign Key?

第一个问题：我可以制作一个也是外键的主键吗？

Second Question: When I try to export the changes it says: Executing SQL script in server

第二个问题：当我尝试导出更改时，它说：在服务器中执行 SQL 脚本

# ERROR: Error 1005: Can't create table 'dbimmobili.condoni' (errno: 150)

CREATE  TABLE IF NOT EXISTS `dbimmobili`.`Condoni` (

  `ComuneImmobile` VARCHAR(50) NOT NULL ,
  `ViaImmobile` VARCHAR(50) NOT NULL ,
  `CivicoImmobile` VARCHAR(5) NOT NULL ,
  `InternoImmobile` VARCHAR(3) NOT NULL ,
  `ProtocolloNumero` VARCHAR(15) NULL ,
  `DataRichiestaSanatoria` DATE NULL ,
  `DataSanatoria` DATE NULL ,
  `SullePartiEsclusive` TINYINT(1) NULL ,
  `SullePartiComuni` TINYINT(1) NULL ,
  `OblazioneInEuro` DOUBLE NULL ,
  `TecnicoOblazione` VARCHAR(45) NULL ,
  `TelefonoTecnico` VARCHAR(15) NULL ,
  INDEX `ComuneImmobile` (`ComuneImmobile` ASC) ,
  INDEX `ViaImmobile` (`ViaImmobile` ASC) ,
  INDEX `CivicoImmobile` (`CivicoImmobile` ASC) ,
  INDEX `InternoImmobile` (`InternoImmobile` ASC) ,

  PRIMARY KEY (`ComuneImmobile`, `ViaImmobile`, `CivicoImmobile`, `InternoImmobile`) ,

  CONSTRAINT `ComuneImmobile`
    FOREIGN KEY (`ComuneImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ComuneImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `ViaImmobile`
    FOREIGN KEY (`ViaImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ViaImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `CivicoImmobile`
    FOREIGN KEY (`CivicoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`CivicoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `InternoImmobile`
    FOREIGN KEY (`InternoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`InternoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB

Showing the Engine Status:

显示引擎状态：

Error in foreign key constraint of table dbimmobili/valutazionimercato:

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or columns typse in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

表 dbimmobili/valutazionimercato 的外键约束错误：

在被引用表中找不到索引，其中被引用列显示为第一列，或者表中的列类型与被引用表的约束不匹配。请注意，在>= InnoDB-4.1.12创建的表中，ENUM和SET的内部存储类型发生了变化，旧表中的此类列不能被新表中的此类列引用。

Where I'm doing wrong?

我哪里做错了？

回答by Daniel Vassallo

When creating a foreign key constraint, MySQL requires a usable index on both the referencing table and also on the referenced table. The index on the referencing table is created automatically if one doesn't exist, but the one on the referenced table needs to be created manually (Source). Yours appears to be missing.

创建外键约束时，MySQL 需要引用表和被引用表上的可用索引。如果引用表上的索引不存在，则会自动创建，但需要手动创建引用表上的索引（Source）。你的似乎不见了。

Test case:

测试用例：

CREATE TABLE tbl_a (
    id int PRIMARY KEY,
    some_other_id int,
    value int
) ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
ERROR 1005 (HY000): Can't create table 'e.tbl_b' (errno: 150)

But if we add an index on some_other_id:

但是如果我们在 上添加一个索引some_other_id：

CREATE INDEX ix_some_id ON tbl_a (some_other_id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

This is often not an issue in most situations, since the referenced field is often the primary key of the referenced table, and the primary key is indexed automatically.

在大多数情况下这通常不是问题，因为被引用的字段通常是被引用表的主键，并且主键会自动建立索引。

回答by dmp

Double check that the foreign keys have exactly the same type as the field you've got in this table. For example, both should be Integer(10), or Varchar (8), even the number of characters.

仔细检查外键的类型是否与您在此表中获得的字段完全相同。例如，两者都应该是 Integer(10) 或 Varchar(8)，甚至是字符数。

回答by Steve

I realize this is an old post, but it ranks high in Google, so I'm adding what I figured out for MY problem. If you have a mix of table types (e.g. MyISAM and InnoDB), you will get this error as well. In this case, InnoDB is the default table type, but one table needed fulltext searching so it was migrated to MyISAM. In this situation, you cannot create a foreign key in the InnoDB table that references the MyISAM table.

我意识到这是一篇旧帖子，但它在谷歌中排名很高，所以我正在添加我为我的问题找到的东西。如果您有混合的表类型（例如 MyISAM 和 InnoDB），您也会收到此错误。在这种情况下，InnoDB 是默认的表类型，但其中一张表需要全文搜索，因此迁移到 MyISAM。在这种情况下，您无法在引用 MyISAM 表的 InnoDB 表中创建外键。

回答by Alon Diamant

If your key is a CHAR/VARCHAR or something of that type, another possible problem is different collation. Check if the charset is the same.

如果您的键是 CHAR/VARCHAR 或某种类型的东西，另一个可能的问题是不同的排序规则。检查字符集是否相同。

回答by Wafje

I had this error and found the reason for the error in my case. I'm still answering to this old post because it ranks pretty high on Google.

我遇到了这个错误，并在我的案例中找到了错误的原因。我仍在回答这个旧帖子，因为它在 Google 上的排名相当高。

The variables of both of the column I wanted to link were integers but one of the ints had 'unsigned' checked on. Simply un-checking that fixed my error.

我想链接的两列的变量都是整数，但其中一个整数已选中“无符号”。只需取消选中即可修复我的错误。

回答by iltaf khalid

I was getting a same error. I found out the solution that I had created the primary key in the main table as BIGINT UNSIGNED and was declaring it as a foreign key in the second table as only BIGINT.

我遇到了同样的错误。我发现我在主表中将主键创建为 BIGINT UNSIGNED 并将其声明为第二个表中的外键的解决方案只有 BIGINT。

When I declared my foreign key as BIGINT UNSIGED in second table, everything worked fine, even didn't need any indexes to be created.

当我在第二个表中将我的外键声明为 BIGINT UNSIGED 时，一切正常，甚至不需要创建任何索引。

So it was a datatype mismatch between the primary key and the foreign key :)

所以这是主键和外键之间的数据类型不匹配:)

回答by Sherlock

I had the exact same problem, but the solution to my problem was entirely different. I had, somewhere else in the database, a foreign key with the same name. That caused the error 1005.

我遇到了完全相同的问题，但解决我的问题的方法完全不同。我在数据库的其他地方有一个同名的外键。这导致了错误 1005。

Renaming my foreign key to something more specific to that situation solved the problem.

将我的外键重命名为更具体的东西解决了这个问题。

回答by Rizwan Mumtaz

In my case the error was due to the referencingtable is MyISAMwhere as referringtable was InnoDB.

在我的情况下，错误是由于referencingtable is MyISAMwhere as referringtable was InnoDB。

Convertedtable engine from MyISAM to InnoDBsolves the problem for me.

Converted来自表引擎MyISAM to InnoDB为我解决了这个问题。

ALTER TABLE table_name ENGINE=InnoDB;

回答by Jason Rundell

1. Make sure that both tables are using the same Engine type.
2. Make sure the fields you are indexing have the same type and length.

1. 确保两个表都使用相同的引擎类型。
2. 确保您正在编制索引的字段具有相同的类型和长度。

回答by sba

Give attention to CHARSETand COLLATEparameters when you create a table. In terms of FOREIGN KEYproblems something like that:

创建表时要注意CHARSET和COLLATE参数。在外键问题方面是这样的：

CREATE TABLE yourTableName (
....
....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In my case i couldn′t create the table with FOREIGN KEY references. First i got the Error Code 1005 which pretty much says nothing. Then i added COLLATEand finally the error message complaining about CHARSET.

就我而言，我无法使用 FOREIGN KEY 引用创建表。首先，我得到了错误代码 1005，它几乎什么也没说。然后我添加了 COLLATE，最后添加了抱怨 CHARSET 的错误消息。

Error Code: 1253. COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'

After that correction my issue was solved.

在那次更正之后，我的问题就解决了。