MySQL 5.5 errno: 150 “外键约束格式不正确"

问题描述

关于这个错误有很多问题，但我似乎找不到任何与我所拥有的相似的场景.

There are plenty of questions regarding this error but I can't seem to find any similar scenario to what I have.

我的第一张桌子(用户):

My 1st table (users):

我的第二张桌子(大学):

My 2nd table (colleges):

我正在尝试更改第一个表并添加一个引用第二个表的 id 的外键:

I am trying to alter 1st table and add a foreign key that references id of a 2nd table:

ALTER TABLE users
ADD CONSTRAINT FOREIGN KEY (collegelinkId)
REFERENCES databaseName.colleges (id);

失败并返回错误 (errno: 150 "Foreign key constraint is wrongly forms").

这两个表之间唯一不同的参数是auto_increment.但是，我无法将 auto_increment 添加到我的用户表 collegelinkId 列，因为它的 id 已设置为 auto_increment.

The only parameter that is different between these two tables is auto_increment. However, I can not add auto_increment to my users table collegelinkId column since its id is already set to auto_increment.

推荐答案

由于列的类型相同，因此值得按照@Tim Biegleisen 的建议检查引擎类型.

Since the columns are of the same type, it's worth to check the engine type as @Tim Biegeleisen suggested.

更改引擎类型解决了这个问题.

Changing engine type fixed the issue.

ALTER TABLE users
ENGINE=InnoDB;

这篇关于MySQL 5.5 errno: 150 “外键约束格式不正确"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！