I have a set of InnoDB tables that I periodically need to maintain by removing some rows and inserting others. Several of the tables have foreign key constraints referencing other tables, so this means that the table loading order is important. To insert the new rows without worrying about the order of the tables, I use:
SET FOREIGN_KEY_CHECKS=0;
before, and then:
SET FOREIGN_KEY_CHECKS=1;
after.
When the loading is complete, I'd like to check that the data in the updated tables still hold referential integrity--that the new rows don't break foreign key constraints--but it seems that there's no way to do this.
As a test, I entered data that I was sure violated foreign key constraints, and upon re-enabling the foreign k开发者_JS百科ey checks, mysql produced no warnings or errors.
If I tried to find a way to specify the table loading order, and left the foreign key checks on during the loading process, this would not allow me to load data in a table that has a self-referencing foreign key constraint, so this would not be an acceptable solution.
Is there any way to force InnoDB to verify a table's or a database's foreign key constraints?
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$
CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);
DECLARE done INT DEFAULT 0;
DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;
OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';');
END IF;
DEALLOCATE PREPARE stmt;
END LOOP foreign_key_cursor_loop;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;
SELECT * FROM INVALID_FOREIGN_KEYS;
You can use this stored procedure to check the all database for invalid foreign keys.
The result will be loaded into INVALID_FOREIGN_KEYS table.
Parameters of ANALYZE_INVALID_FOREIGN_KEYS:
- Database name pattern (LIKE style)
- Table name pattern (LIKE style)
Whether the result will be temporary. It can be:
'Y','N',NULL.- In case of
'Y'theANALYZE_INVALID_FOREIGN_KEYSresult table will be temporary table. The temporary table won't be visible for other sessions. You can execute multipleANALYZE_INVALID_FOREIGN_KEYS(...)stored procedure parallelly with temporary result table. - But if you are interested in the partial result from an other session, then you must use
'N', then executeSELECT * FROM INVALID_FOREIGN_KEYS;from an other session. You must use
NULLto skip result table creation in transaction, because MySQL executes implicit commit in transaction forCREATE TABLE ...andDROP TABLE ..., so the creation of result table would cause problem in transaction. In this case you must create the result table yourself out ofBEGIN; COMMIT/ROLLBACK;block:CREATE TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) );Visit MySQL site about implicit commit: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
- In case of
The INVALID_FOREIGN_KEYS rows will contain only the name of invalid database, table, column. But you can see the invalid referring rows with the execution of value of INVALID_KEY_SQL column of INVALID_FOREIGN_KEYS if there is any.
This stored procedure will be very fast if there are indexes on the referring columns (aka. foreign index) and on the referred columns (usually primary key).
Thanks for this great answer - this is a very handy tool. Here is a slightly modified version of the procedure that includes SQL in the output table to delete keys with invalid keys - handy for the cases where you have confirmed that these rows are simply orphans from missing/disabled delete cascade rules (and not orphans from primary key changes or other more complex cases).
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$
CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);
DECLARE done INT DEFAULT 0;
DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024),
`INVALID_KEY_DELETE_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024),
`INVALID_KEY_DELETE_SQL` VARCHAR(1024)
);
END IF;
OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';'),
`INVALID_KEY_DELETE_SQL` = CONCAT('DELETE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '` ',
'FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' ',
'ON (', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' = ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL', ';');
END IF;
DEALLOCATE PREPARE stmt;
END LOOP foreign_key_cursor_loop;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;
SELECT * FROM INVALID_FOREIGN_KEYS;
I modified the script to handle multiple columns foreign keys.
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$
CREATE PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
IN `checked_database_name` VARCHAR(64),
IN `checked_table_name` VARCHAR(64),
IN `temporary_result_table` ENUM('Y', 'N')
)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;
SET @prev_constraint_name = '';
SET @prev_table_schema = '';
SET @prev_table_name = '';
SET @prev_referenced_table_schema = '';
SET @prev_referenced_table_name = '';
SET @from_part = '';
SET @where_part = '';
SET @where_nullable = '';
SET @all_columns = '';
SET @all_referenced_columns = '';
OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;
IF (@prev_constraint_name <> CONSTRAINT_NAME_VAR AND @from_part <> '' AND @where_part <> '') THEN
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = @prev_table_schema,
`TABLE_NAME` = @prev_table_name,
`COLUMN_NAME` = @all_columns,
`CONSTRAINT_NAME` = @prev_constraint_name,
`REFERENCED_TABLE_SCHEMA` = @prev_referenced_table_schema,
`REFERENCED_TABLE_NAME` = @prev_table_name,
`REFERENCED_COLUMN_NAME` = @all_referenced_columns,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = @invalid_query;
END IF;
DEALLOCATE PREPARE stmt;
SET @where_part = '';
SET @where_nullable = '';
SET @all_columns = '';
SET @all_referenced_columns = '';
END IF;
IF (LENGTH(@where_part) > 0) THEN
SET @where_nullable = CONCAT(@where_nullable, ' OR ');
SET @where_part = CONCAT(@where_part, ' AND ');
SET @all_columns = CONCAT(@all_columns, ', ' COLUMN_NAME_VAR);
SET @all_referenced_columns = CONCAT(@all_referenced_columns, ', ' REFERENCED_COLUMN_NAME_VAR);
ELSE
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ');
SET @from_where_part = CONCAT('NOT EXISTS (SELECT * FROM `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ');
SET @all_columns = COLUMN_NAME_VAR;
SET @all_referenced_columns = REFERENCED_COLUMN_NAME_VAR;
END IF;
SET @where_nullable = CONCAT(@where_nullable, 'REFERRING.', COLUMN_NAME_VAR, ' IS NOT NULL');
SET @where_part = CONCAT(@where_part, 'REFERRING.', COLUMN_NAME_VAR, ' = ', 'REFERRED.', REFERENCED_COLUMN_NAME_VAR);
SET @prev_constraint_name = CONSTRAINT_NAME_VAR;
SET @prev_table_schema = TABLE_SCHEMA_VAR;
SET @prev_table_name = TABLE_NAME_VAR;
SET @prev_referenced_table_schema = REFERENCED_TABLE_SCHEMA_VAR;
SET @prev_referenced_table_name = REFERENCED_TABLE_NAME_VAR;
END LOOP foreign_key_cursor_loop;
IF (@where_part <> '' AND @from_part <> '') THEN
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = @prev_table_schema,
`TABLE_NAME` = @prev_table_name,
`COLUMN_NAME` = @all_columns,
`CONSTRAINT_NAME` = @prev_constraint_name,
`REFERENCED_TABLE_SCHEMA` = @prev_referenced_table_schema,
`REFERENCED_TABLE_NAME` = @prev_table_name,
`REFERENCED_COLUMN_NAME` = @all_referenced_columns,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = @invalid_query;
END IF;
DEALLOCATE PREPARE stmt;
END IF;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;
SELECT * FROM INVALID_FOREIGN_KEYS;
There is no tool, that can do that. But you can write a script, that will walk through all your tables, drop and recreate foreign key constrains. On recreation, there will be an error if something is wrong.
The same check but for invalid UNIQUE keys analysis:
--> Small bug/feature: It will report duplicate nulls also. (While mysql allows duplicate nulls).
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS$$
CREATE
PROCEDURE `ANALYZE_INVALID_UNIQUE_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAMES_VAR VARCHAR(1000);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE unique_key_cursor CURSOR FOR
select kcu.table_schema sch,
kcu.table_name tbl,
group_concat(kcu.column_name) colName,
kcu.constraint_name constName
from
information_schema.table_constraints tc
join
information_schema.key_column_usage kcu
on
kcu.constraint_name=tc.constraint_name
and kcu.constraint_schema=tc.constraint_schema
and kcu.table_name=tc.table_name
where
kcu.table_schema like checked_database_name
and kcu.table_name like checked_table_name
and tc.constraint_type="UNIQUE" group by sch, tbl, constName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TEMPORARY TABLE IF EXISTS INVALID_UNIQUE_KEYS;
CREATE TEMPORARY TABLE INVALID_UNIQUE_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAMES` VARCHAR(1000),
`CONSTRAINT_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT
);
OPEN unique_key_cursor;
unique_key_cursor_loop: LOOP
FETCH unique_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAMES_VAR,
CONSTRAINT_NAME_VAR;
IF done THEN
LEAVE unique_key_cursor_loop;
END IF;
SET @from_part = CONCAT('FROM (SELECT COUNT(*) counter FROM', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`',
' GROUP BY ', COLUMN_NAMES_VAR , ') as s where s.counter > 1');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_UNIQUE_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAMES` = COLUMN_NAMES_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count;
END IF;
DEALLOCATE PREPARE stmt;
END LOOP unique_key_cursor_loop;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_UNIQUE_KEYS('%', '%');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS;
SELECT * FROM INVALID_UNIQUE_KEYS;
![Interactive visualization of a graph in python [closed]](https://www.devze.com/res/2023/04-10/09/92d32fe8c0d22fb96bd6f6e8b7d1f457.gif)
加载中,请稍侯......
精彩评论