MySQL 错误代码 1452 外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22210461/
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
MySQL Error Code 1452 Foreign Key Constraint
提问by Archibald
I'm receiving an error when I attempt to create two tables. There was a multivalued dependency, so I separated the tables and came up with this:
当我尝试创建两个表时收到错误。有一个多值依赖关系，所以我把表格分开并想出了这个：
CREATE TABLE NAME (
NameID Integer NOT NULL AUTO_INCREMENT,
Name varChar(255) NOT NULL,
CONSTRAINT NAME_PK PRIMARY KEY(NameID)
);
CREATE TABLE PHONE (
NameID Integer NOT NULL,
PhoneNumber varChar(15) NOT NULL,
NumType varChar(5) NOT NULL,
CONSTRAINT PHONE_FK FOREIGN KEY(NameID)
REFERENCES NAME(NameID),
CONSTRAINT PHONE_PK PRIMARY KEY(NameID)
);
But when attempting to add values with this code:
但是当尝试使用此代码添加值时：
INSERT INTO NAME (NameID, Name) VALUES (default, 'John Doe');
INSERT INTO PHONE (NameID, PhoneNumber, NumType) VALUES (default, '706-782-4719', 'Home');
I receive the infamous 1452 error:
我收到臭名昭著的 1452 错误：
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`phone_mcneill`.`PHONE`, CONSTRAINT `PHONE_FK` FOREIGN KEY (`NameID`) REFERENCES `NAME` (`NameID`))
I am not entirely sure what this means as I have NameID autoincrementing in the first table. I can't have it auto_increment in the second one as well as it's a foreign key, correct? Thanks in advance for the help.
我不完全确定这意味着什么，因为我在第一个表中有 NameID 自动递增。我不能在第二个中使用 auto_increment 以及它是一个外键，对吗？在此先感谢您的帮助。
回答by M Khalid Junaid
You have defined a foreign key constraint on NameIDcolumn i.e in table PHONEusing insert for phone table you have passed default against NameID ,but NameID is pointing to NAMEtable and expecting to have the inserted record id from NAMEtable it doesn't have a default value as per the docs
您已经在NameID列上定义了一个外键约束，即在表中PHONE使用插入电话表您已经通过默认值反对 NameID ，但 NameID 指向NAME表并期望从NAME表中插入记录 id它没有按照默认值文档
When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID()
当一个新的 AUTO_INCREMENT 值产生后，你也可以通过执行 SELECT LAST_INSERT_ID() 来获取它
So your second insert can use the inserted if from NAMEtable like
所以你的第二个插入可以使用插入的 if from NAMEtable like
INSERT INTO NAME (NameID, NAME) VALUES (DEFAULT, 'John Doe');
INSERT INTO PHONE (NameID, PhoneNumber, NumType) VALUES (LAST_INSERT_ID(), '706-782-4719', 'Home');
And you can grab results from both tables by joining them
您可以通过加入两个表来获取结果
select * from NAME
JOIN PHONE
USING (NameID)
