MySQL InnoDB - create CASCADE foreign key constraint when data already exists that violates it?

Question

I have a database that was originally in MyISAM and so had no foreign key constraints. As a result, there are quite a few orphaned rows where the record the foreign key refers to has since been deleted.

To fix this I decided to convert to InnoDB and add the foreign keys, with CASCADE for both updating and deleting. However, when I try to add the foreign key, I get errors like this (from Navicat 8 console):

1452 - Cannot add or update a child row: a foreign key constraint fails 
(`database`.`#sql-1358_38d`, CONSTRAINT `#sql-1358_38d_ibfk_1` FOREIGN KEY 
(`manufacturerID`) REFERENCES `manufacturer` (`ID`) ON DE)

I know why it's doing this - because of the orphaned rows. Is there a way though to have the creation of the constrain automatically clear out those rows? It will take ages to go through all of the tables and find orphaned rows.

This is one of the queries I'm running just in case it is suspect:

ALTER TABLE part_number ADD CONSTRAINT
FOREIGN KEY(manufacturerID)
REFERENCES manufacturer(ID)
ON DELETE CASCADE
ON UPDATE CASCADE;

Answer 1

write a query that finds the orphaned rows and then use that to delete. e.g

SELECT part_number.id FROM part_number LEFT JOIN manufacturer ON (manufacturer.ID = part_number.manufacturerID) where manufacturer.ID IS NULL

Answer 2

You need to get rid of all irrelevant records before you can add a constraint.

You can do it two ways.

1 Using not exists()

DELETE FROM part_number
 WHERE NOT EXISTS (
           select id from manufacturer
            where part_number.manufacturerID = manufacturer.ID
       )

2. Using a temporary table (a bit ugly, but i'll post it too)

-- create a temporary table
CREATE TEMPORARY TABLE `temp_ids`
(
  `id` INTEGER
);

-- store all id's that need to be deleted
INSERT INTO `temp_ids`
SELECT part_number.id FROM part_number LEFT JOIN manufacturer ON (manufacturer.ID = part_number.manufacturerID)
WHERE ISNULL(`manufacturer.ID);

-- delete them
DELETE
FROM part_number
WHERE id IN (
  SELECT `id` FROM `temp_ids`
);

-- drop the table
DROP TEMPORARY TABLE `temp_ids`;

See my related question: Handling database integrity

After all "dead" records are deleted, you will be able to add a constraint.

Hope this works for you :)