MySQL 外键约束形成不正确？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10078111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Foreign key constraint is incorrectly formed?
提问by user1235872
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!
感谢帮助！
回答by a_horse_with_no_name
That's because the primary key of the notestable 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 列。
回答by PHPst
Also make sure that both tables are innoDB.
还要确保两个表都是 innoDB。
回答by Abdelrahman Aly
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.
它会起作用。
回答by Ali Gangji
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 varcharfield and an intfield.
除了已给出的答案外，如果字段类型不匹配，您也会收到此错误。例如，如果您尝试在varchar字段和int字段之间创建外键约束。
回答by Roman Puchkovskiy
One more addition: charsets of the fields must match.
再补充一点：字段的字符集必须匹配。
In the referenced table I had ascii as a default charset: DEFAULT CHARSET=asciiwas reported by show create table. I tried to create the referencing table with DEFAULT CHARSET=utfand I got 'Foreign key constraint is incorrectly formed'.
在引用的表中，我将 ascii 作为默认字符集：DEFAULT CHARSET=ascii由show create table. 我尝试使用创建引用表，DEFAULT CHARSET=utf但得到“外键约束的格式不正确”。
After I changed this to DEFAULT CHARSET=asciion the new table (the referencing one), it was created successfully.
在我将其更改DEFAULT CHARSET=ascii为新表（引用表）后，它已成功创建。
