I don't know of any backup solution that respects the order of the tables in regards to foreign keys and doesn't simply disable foreign key checks when importing the data.
Therefore you have to check for data inconsistencies yourself.
You can do this for example with the following procedure:
DROP PROCEDURE IF EXISTS sp_checkFKIntegrity;
DELIMITER $$
CREATE PROCEDURE sp_checkFKIntegrity(
IN p_schemaname varchar(255),
IN p_tablename varchar(255)
)
BEGIN
DECLARE v_table_schema varchar(255);
DECLARE v_table_name varchar(255);
DECLARE v_referenced_table_schema varchar(255);
DECLARE v_referenced_table_name varchar(255);
DECLARE v_column_name varchar(255);
DECLARE v_referenced_column_name varchar(255);
DECLARE v_constraint_name varchar(255);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
SELECT
table_schema,
table_name,
referenced_table_schema,
referenced_table_name,
column_name,
referenced_column_name,
constraint_name
FROM
information_schema.key_column_usage k
WHERE k.table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'common')
AND referenced_table_name IS NOT NULL
AND table_schema = COALESCE(p_schemaname, table_schema)
AND table_name = COALESCE(p_tablename, table_name);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SELECT
COUNT(*) INTO @countFKs
FROM
information_schema.key_column_usage k
WHERE k.table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'common')
AND referenced_table_name IS NOT NULL
AND table_schema = COALESCE(p_schemaname, table_schema)
AND table_name = COALESCE(p_tablename, table_name);
SET @counter := 1;
OPEN cur;
cur_loop: LOOP
FETCH cur INTO v_table_schema, v_table_name, v_referenced_table_schema, v_referenced_table_name, v_column_name, v_referenced_column_name, v_constraint_name;
IF done THEN
LEAVE cur_loop;
END IF;
SET @query := CONCAT('SELECT EXISTS(
SELECT 1 FROM ', v_table_schema, '.', v_table_name, ' k
LEFT JOIN ', v_referenced_table_schema, '.', v_referenced_table_name, ' e ON k.', v_column_name, ' = e.', v_referenced_column_name,
' WHERE e.', v_referenced_column_name, ' IS NULL) INTO @consistency;');
SELECT CONCAT('Checking FK constraint ', @counter, ' of ', @countFKs, '...') AS info;
SET @counter := @counter + 1;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @consistency = 1 THEN
SELECT CONCAT('Inconsistency found in foreign key "', v_table_schema, '.', v_table_name, '(', v_column_name, ') -> ', v_referenced_table_schema, '.', v_referenced_table_name, '(', v_referenced_column_name, ')", constraint_name "', v_constraint_name, '"') AS info;
SELECT CONCAT('SELECT COUNT(*) FROM ', v_table_schema, '.', v_table_name, ' k LEFT JOIN ', v_referenced_table_schema, '.', v_referenced_table_name, ' e ON k.', v_column_name, ' = e.', v_referenced_column_name,
' WHERE e.', v_referenced_column_name, ' IS NULL;') AS "Statement for further investigation";
END IF;
END LOOP;
CLOSE cur;
SELECT 'completed OK' AS info;
END $$
DELIMITER ;
The schema and table name parameters are optional, so you can execute the procedure like this:
CALL sp_checkFKIntegrity('playground', null); -- checks all FK constraints in the playground schema
CALL sp_checkFKIntegrity('playground', 'foo'); -- checks the FK constraints the table foo in the playground schema has to other (parent) tables
CALL sp_checkFKIntegrity(null, null); -- checks all FKs except the schemas mentioned in the procedure
CALL sp_checkFKIntegrity(null, 'foo'); -- checks the FK constraints the table foo has to other tables, no matter in which schema it is
What this procedure does not yet support is foreign keys with multiple columns. I was too lazy for that and although possible I haven't seen this in the wild.