SQL query error:

ALTER TABLE `place` ADD FOREIGN KEY (`how`) REFERENCES `soc_pasport`.`place_how`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

MySQL Answer: Documentation

 #1452 - Cannot add or update a child row: a foreign key constraint fails (`soc_pasport`.`#sql-19e0_10a`, CONSTRAINT `#sql-19e0_10a_ibfk_1` FOREIGN KEY (`how`) REFERENCES `place_how` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
  • select * from place where how not in (select id from place_how) at least one record? - Zufir
  • No, it does not issue .. - As Lan
  • Are the field types the same? And both fields are not null? - Akina
  • yes match. yes not null - As Lan
  • 1452 is the data error. That is, according to the structure, the server has no complaints, but the current content does not pass the check. I recommend to create duplicate tables (empty - CREATE TABLE t2 LIKE t1 ), add this link, and then load the data from the originals (first one, then a lot) with the INSERT IGNORE query. After that, see which records in the "many" table were not copied, and try to understand for what reason. - Akina

1 answer 1

This should help

 SET FOREIGN_KEY_CHECKS=0; ALTER TABLE `place` ADD FOREIGN KEY (`how`) REFERENCES `soc_pasport`.`place_how`(`id`) ON DELETE CASCADE ON UPDATE CASCADE; SET FOREIGN_KEY_CHECKS=1;
  • It is fraught with data loss. - Akina
  • @Akina I agree, but there should be no loss from comments on the question from @AsLan - Alexus