Foreign key constraint fails with Insert Into Select in MySQL

Question

I'm quite new to SQL, and I'm trying to upload data to my tables. For this I have special tables where I upload the data from a CSV file, and then, from this table, I am trying to copy the data to the final table.

But now I have a problem with an intermediate table where I have uploaded my data. The table is:

CREATE TABLE `_work_has_person` (
  `work_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `primary_contribution_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`work_id`,`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to copy the data in

CREATE TABLE `work_has_person` (
  `work_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `primary_contribution_id` int(11) NOT NULL,
  PRIMARY KEY (`work_id`,`person_id`),
  KEY `fk_work_has_person_person1_idx` (`person_id`),
  KEY `fk_work_has_person_work1_idx` (`work_id`),
  KEY `fk_work_has_person_primary_contribution1_idx` (`primary_contribution_id`),
  CONSTRAINT `fk_work_has_person_person1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_work_has_person_primary_contribution1` FOREIGN KEY (`primary_contribution_id`) REFERENCES `primary_contribution` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_work_has_person_work1` FOREIGN KEY (`work_id`) REFERENCES `work` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Which is an intermediate table between:

CREATE TABLE `work` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title_work` varchar(250) DEFAULT NULL,
  `subtitle_work` varchar(250) DEFAULT NULL,
  `date_work` varchar(45) DEFAULT NULL,
  `unix_date_work` varchar(100) DEFAULT NULL,
  `sinopsis` text,
  `ref_bne` varchar(100) DEFAULT NULL,
  `ref_alt` longtext,
  `language_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_work_language1_idx` (`language_id`),
  KEY `title_work` (`title_work`),
  CONSTRAINT `fk_work_language1` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=24610 DEFAULT CHARSET=utf8;

and

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `img_person` varchar(250) DEFAULT NULL,
  `born_date` varchar(45) DEFAULT NULL,
  `unix_born_date` varchar(100) DEFAULT NULL,
  `city_born_date` varchar(100) DEFAULT NULL,
  `country_born_date` varchar(100) DEFAULT NULL,
  `death_date` varchar(45) DEFAULT NULL,
  `unix_death_date` varchar(100) DEFAULT NULL,
  `city_death_date` varchar(100) DEFAULT NULL,
  `country_death_date` varchar(45) DEFAULT NULL,
  `biography` longtext,
  `ref_bne` varchar(100) DEFAULT NULL,
  `ref_alt` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9159 DEFAULT CHARSET=utf8;

But everytime I try to run

INSERT INTO work_has_person (work_id, person_id, primary_contribution_id)
SELECT work_id, person_id, primary_contribution_id
FROM _work_has_person;

It says

Cannot add or update a child row: a foreign key constraint fails (`cdu93hfg93r`.
`work_has_person`, CONSTRAINT `fk_work_has_person_person1` FOREIGN KEY (`person_id`) 
REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

I am pretty sure that the tables has the neccesary data, but, ¿is there a way to know which data fails? I have seen Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails: but don't understand exactly how to use it here.

A.

Use something like `select person_id from work_has_person minus select id from person` to figure out unrecognized persons. — Amir Pashazadeh, May 30 '17 at 18:48

score 0 · Accepted Answer · answered May 30 '17 at 18:51

It is relatively easy to find out what data is causing the conflict: get all person_ids from _work_has_person that are not in the persons table. You can achieve this via an outer join and filtering for person.id is null in the where clause.

select * from `_work_has_person` whp
left join person p on whp.person_id=p.id
where p.id is null

You can actually remove such data from the results being inserted by including the reverse criterion into the select part of your insert query (an inner join):

INSERT INTO work_has_person (work_id, person_id, primary_contribution_id)
SELECT whp.work_id, whp.person_id, whp.primary_contribution_id
FROM _work_has_person whp
INNER join person p on whp.person_id=p.id

Thank-you! And not only for the query, but for the explaining as well. Now I understand what I have to do in this situations (and this is a very common one). I was trying to do `SELECT * FROM _work_has_person a LEFT JOIN person b ON a.person_id=b.person_id WHERE p.id IS NULL`, which is quite stupid… — Antoine, May 30 '17 at 18:57

Foreign key constraint fails with Insert Into Select in MySQL

1 Answers1