MySQL错误代码1452外键约束 [英] MySQL Error Code 1452 Foreign Key Constraint

问题描述

我尝试创建两个表时收到错误。有一个多值的依赖，所以我分开的表，并提出了这样：

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');

我收到臭名昭着的1452错误：

I receive the infamous 1452 error:

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`))

我不完全确定这是什么意思，因为我有NameID autoincrementing在第一个表中。我不能有它auto_increment在第二个，以及它是一个外键，正确吗？感谢您提供帮助。

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 column ie在表 PHONE 中使用插入手机表您已经传递默认对NameID，但NameID指向 NAME table，并且希望从 NAME 表中插入记录ID，它没有按照docs的默认值

You have defined a foreign key constraint on NameID column i.e in table PHONE using insert for phone table you have passed default against NameID ,but NameID is pointing to NAME table and expecting to have the inserted record id from NAME table it doesn't have a default value as per the docs

当生成一个新的AUTO_INCREMENT值时，您也可以通过执行SELECT LAST_INSERT_ID（）

When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID()

因此，您的第二个插入可以使用 NAME 表插入的

So your second insert can use the inserted if from NAME table 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)

如何获取上次插入行的唯一ID

查看小说演示

这篇关于MySQL错误代码1452外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！