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

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?

Solution

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.