MySQL 错误代码 1452 外键约束

2022-01-20 00:00:00 sql database foreign-keys mysql mysql-error-1452

我在尝试创建两个表时收到错误消息.有一个多值依赖，所以我将表分开并想出了这个:

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 自动递增.我不能在第二个中使用它 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 列上定义了外键约束，即在表 PHONE 中使用插入电话表您已经针对 NameID 传递了默认值，但 NameID 指向 NAME 表并期望从 NAME 表中插入记录 ID，它没有根据文档

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 表中插入的 if

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最后插入的行

查看小提琴演示

相关文章