MySQL add row from child row if not exists in parent table with error 1452

Question

I have two tables like these:

CREATE TABLE parentTable(
pID    INT,
ArtNr  INT,
Name   VARCHAR(50),
PRIMARY KEY(ID),
UNIQUE KEY (ArtNr)
);

and

CREATE TABLE childTable(
cID       INT,
ArtNr     INT,
Name      VARCHAR(50),
UNIQUE KEY (ArtNr),
FOREIGN KEY (ArtNr) REFERENCES parentTable(ArtNr) ON UPDATE CASCADE ON DELETE CASCADE
);

Now the problem which I have is, that if there is an entry in my childTable with a ArtNr which is not found in my parentTable, I get this error message:

1452 Cannot add or update a child row: a foreign key constraint fails (`sys`.`childTable`, CONSTRAINT `childTable_ibfk_1` FOREIGN KEY (`ArtNr`) REFERENCES `parentTable` (`ArtNr`) ON DELETE CASCADE ON UPDATE CASCADE)

What I want to do is, to add the row if the entry in the childTable is not found in my parentTable. Is it possible to do that?

Answer 1

You can use server variable FOREIGN_KEY_CHECKS to on/off foreign key value checking.

For example:

SET FOREIGN_KEY_CHECKS=0;

// ...
// your script...
// ...

SET FOREIGN_KEY_CHECKS=1;

More information - foreign_key_checks.

---

You can find missing entries using this query -

SELECT c.* FROM child c
LEFT JOIN parent p
  ON p.id = c.id
WHERE p.id IS NULL;

Then insert these set of ID into parent table. Do it with a help of INSERT..SELECT statement.