外键约束是否错误形成？

I got this errore when create table: Foreign key constraint is incorrectly formed???

创建表时我得到了这个错误：外键约束形成错误???

{create table comment(
Comment_ID int UNSIGNED AUTO_INCREMENT not null,
User_1 varchar(50) not null,
Note_ID int(11) UNSIGNED not null,
PRIMARY key(Comment_ID),
  CONSTRAINT `fk_1` FOREIGN KEY (`User_1`) REFERENCES `user` (`Dev_ID`),
  CONSTRAINT `fk_2` FOREIGN KEY (`User_2`) REFERENCES `user` (`Dev_ID`),
  CONSTRAINT `fk_3` FOREIGN KEY (`Note_ID`) REFERENCES `note`(`Note_ID`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
}

it's ok when i remove fk_3
this my note table

当我删除fk_3这个我的笔记表时，这没关系

{ 
CREATE TABLE `note` (
 `Dev_ID` varchar(50) NOT NULL,
  `Note_ID` int(11) UNSIGNED NOT NULL,
  `Title` varchar(200) NOT NULL,
  `Time` datetime NOT NULL,
  `Mood` int(11) NOT NULL,
  `Body` varchar(3000) NOT NULL,
  `Visible` tinyint(1) NOT NULL DEFAULT '1',
  `Share` tinyint(1) NOT NULL DEFAULT '0',
  `Update` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Dev_ID`,`Note_ID`),
  CONSTRAINT `fk_note_user` FOREIGN KEY (`Dev_ID`) REFERENCES `user` (`Dev_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
}

Thanks for help!

感谢帮助！

5 个解决方案

#1

That's because the primary key of the notes table is (Dev_ID,Note_ID) but you are only referencing one of those columns (Note_ID) in your constraint.

这是因为notes表的主键是（Dev_ID，Note_ID），但是您只在约束中引用其中一列（Note_ID）。

A FK constraint must always consist of all PK columns.

FK约束必须始终包含所有PK列。

#2

Also make sure that both tables are innoDB.

还要确保两个表都是innoDB。

#3

In addition to the answers that have been given, you would also get this error if the field types did not match. For example, if you tried to create a foreign key constraint between a varchar field and an int field.

除了已给出的答案之外，如果字段类型不匹配，您还会收到此错误。例如，如果您尝试在varchar字段和int字段之间创建外键约束。

#4

One more addition: charsets of the fields must match.

还有一个补充：字段的字符集必须匹配。

In the referenced table I had ascii as a default charset: DEFAULT CHARSET=ascii was reported by show create table. I tried to create the referencing table with DEFAULT CHARSET=utf and I got 'Foreign key constraint is incorrectly formed'.

在引用的表中，我将ascii作为默认字符集：show create table报告了DEFAULT CHARSET = ascii。我试图用DEFAULT CHARSET = utf创建引用表，我得到'外键约束不正确'。

After I changed this to DEFAULT CHARSET=ascii on the new table (the referencing one), it was created successfully.

在新表（引用一个）上将其更改为DEFAULT CHARSET = ascii后，它已成功创建。

#5

This problem occur because the column

出现此问题是因为列

`Note_ID` int(11) UNSIGNED NOT NULL

Is neither primary nor unique.

既不是主要也不是唯一。

Just make it

做吧

`Note_ID` int(11) UNSIGNED NOT NULL UNIQUE

And it will work.

它会工作。

#1