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

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

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.

回答1:

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

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

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)

How to Get the Unique ID for the Last Inserted Row

See fiddle Demo