Description:
In MySQL, we can have foreign keys referencing to different schema.
Example:
MySQL [(none)]> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, TABLE_NAME, REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS;
+-------------------+--------------------+--------------------------+-------------+-----------------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_SCHEMA | TABLE_NAME | REFERENCED_TABLE_NAME |
+-------------------+--------------------+--------------------------+-------------+-----------------------+
| collection | collect_101_ibfk_1 | sample | collect_101 | sample_101 |
| collection | collect_102_ibfk_1 | collection | collect_102 | collect_101 |
+-------------------+--------------------+--------------------------+-------------+-----------------------+
2 rows in set (0.001 sec)
When executing rechecks using mysqlsh with util check-for-server-upgrade, it executes "foreignKeyReferences" check which Checks for foreign keys not referencing a full unique index. This check only identifies foreign keys referenced to with in same schema and ignore foreign keys referencing to different schema.
[root@testbox data]# mysqlsh -- util check-for-server-upgrade --user=root --host=localhost --password=**** --socket=/tmp/mysql_sandbox8042.sock --target-version=8.4.8
The MySQL server at /tmp%2Fmysql_sandbox8042.sock, version 8.0.42 - MySQL
Community Server - GPL, will now be checked for compatibility issues for
upgrade to MySQL 8.4.8.
.
.
4) Checks for foreign keys not referencing a full unique index
(foreignKeyReferences)
Foreign keys to partial indexes may be forbidden as of 8.4.0, this check
identifies such cases to warn the user.
collection.collect_102_ibfk_1 - invalid foreign key defined as
'collect_102(k)' references a non unique key at table 'collect_101'.
Solutions:
.
.
[root@testbox data]#
When we check the actual query and the result clearly shows that foreign key referring to different schema excluded in the output:
MySQL [(none)]> select fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition as fk_definition, fk.REFERENCED_TABLE_NAME as target_table, '##fkToNonUniqueKey' from (select rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition, CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition, rc.REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS rc join
information_schema.KEY_COLUMN_USAGE kc on rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND rc.REFERENCED_TABLE_NAME
= kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME is not NULL AND kc.REFERENCED_COLUMN_NAME is not NULL where (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) group by rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME) fk join (SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition, SUM(non_unique) as non_unique_count FROM INFORMATION_SCHEMA.STATISTICS WHERE (table_schema NOT IN('sys','mysql','performance_schema','information_schema'))
AND sub_part IS NULL GROUP BY table_schema, table_name, index_name) idx on fk.target_fk_definition = idx.fk_definition AND idx.non_unique_count > 0;
+-------------------+--------------------+--+----------------+--------------+--------------------+
| constraint_schema | constraint_name | | fk_definition | target_table | ##fkToNonUniqueKey |
+-------------------+--------------------+--+----------------+--------------+--------------------+
| collection | collect_102_ibfk_1 | | collect_102(k) | collect_101 | ##fkToNonUniqueKey |
+-------------------+--------------------+--+----------------+--------------+--------------------+
1 row in set (0.004 sec)
NOTE: Same issue exists for another query validating fkToPartialKey as well.
SELECT constraint_schema, name, '', fk_definition, col_list, target_table, '##fkToPartialKey' FROM ( SELECT rc.constraint_schema constraint_schema, rc.constraint_name name, rc.referenced_table_name target_table, CONCAT(kc.table_schema,'.',rc.table_name,'(',GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') fk_definition, CONCAT(kc.referenced_table_schema,'.',kc.referenced_table_name,'(',GROUP_CONCAT(kc.referenced_column_name order by kc.ORDINAL_POSITION),')') col_list FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc ON rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.table_name = kc.table_name AND
rc.referenced_table_name = kc.referenced_table_name AND (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) GROUP BY rc.constraint_name, rc.constraint_schema, kc.table_schema, kc.table_name, kc.referenced_table_schema, kc.referenced_table_name ) fk WHERE fk.col_list NOT IN ( SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') col_list FROM INFORMATION_SCHEMA.STATISTICS WHERE
sub_part IS NULL AND (table_schema NOT IN('sys','mysql','performance_schema','information_schema')) GROUP BY table_schema, table_name, index_name );
How to repeat:
=========
Create 8.0.42 instance and Create tables with below definition and then execute mysqlsh upgrade prechecks.
CREATE DATABASE IF NOT EXISTS sample;
CREATE DATABASE IF NOT EXISTS collection;
CREATE TABLE IF NOT EXISTS `sample`.`sample_101` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS `collection`.`collect_101` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`blob_column` blob,
PRIMARY KEY (`id`),
KEY `k` (`k`),
CONSTRAINT `collect_101_ibfk_1` FOREIGN KEY (`k`) REFERENCES `sample`.`sample_101` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS `collection`.`collect_102` (
`id` int NOT NULL,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`blob_column` blob,
KEY `k` (`k`),
CONSTRAINT `collect_102_ibfk_1` FOREIGN KEY (`k`) REFERENCES `collect_101` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Run mysql shell to upgrade to 8.4
mysqlsh -- util check-for-server-upgrade --user=root --host=localhost --password=***** --socket=/tmp/mysql_sandbox8042.sock --target-version=8.4.8
(OR)
run below query
select
fk.constraint_schema,
fk.constraint_name,
'',
fk.parent_fk_definition as fk_definition,
fk.REFERENCED_TABLE_NAME as target_table,
'##fkToNonUniqueKey'
from (select
rc.constraint_schema,
rc.constraint_name,
CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition,
CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition,
rc.REFERENCED_TABLE_NAME
from
information_schema.REFERENTIAL_CONSTRAINTS rc
join
information_schema.KEY_COLUMN_USAGE kc
on
rc.constraint_schema = kc.constraint_schema AND
rc.constraint_name = kc.constraint_name AND
rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND
rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND
kc.REFERENCED_TABLE_NAME is not NULL AND
kc.REFERENCED_COLUMN_NAME is not NULL
where
rc.constraint_schema not in ('mysql','information_schema','performance_schema','sys')
group by
rc.constraint_schema,
rc.constraint_name,
rc.table_name,
rc.REFERENCED_TABLE_NAME) fk
join (SELECT
CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition,
SUM(non_unique) as non_unique_count
FROM
INFORMATION_SCHEMA.STATISTICS
WHERE
table_schema not in ('mysql','information_schema','performance_schema','sys') AND
sub_part IS NULL
GROUP BY
table_schema, table_name, index_name) idx
on
fk.target_fk_definition = idx.fk_definition AND
idx.non_unique_count > 0;
Suggested fix:
When we removed condition rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA and include kc.referenced_table_schema in the definition of derived table fk then the query works as expected:
MySQL [(none)]> select fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition as fk_definition, fk.REFERENCED_TABLE_NAME as target_table, '##fkToNonUniqueKey' from (select rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition, CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition, rc.REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS rc join
information_schema.KEY_COLUMN_USAGE kc on rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME is not NULL AND kc.REFERENCED_COLUMN_NAME is not NULL where (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) group by rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME, kc.referenced_table_schema) fk join (SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition, SUM(non_unique) as non_unique_count FROM INFORMATION_SCHEMA.STATISTICS WHERE (table_schema NOT IN('sys','mysql','performance_schema','information_schema')) AND sub_part IS NULL
GROUP BY table_schema, table_name, index_name) idx on fk.target_fk_definition = idx.fk_definition AND idx.non_unique_count > 0;
+-------------------+--------------------+--+----------------+--------------+--------------------+
| constraint_schema | constraint_name | | fk_definition | target_table | ##fkToNonUniqueKey |
+-------------------+--------------------+--+----------------+--------------+--------------------+
| collection | collect_102_ibfk_1 | | collect_102(k) | collect_101 | ##fkToNonUniqueKey |
| collection | collect_101_ibfk_1 | | collect_101(k) | sample_101 | ##fkToNonUniqueKey |
+-------------------+--------------------+--+----------------+--------------+--------------------+
2 rows in set (0.006 sec)
To further enhance the output, we can include the schema name followed by table name in the target_table column listing.
MySQL [(none)]> select fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition as fk_definition, substring_index(fk.target_fk_definition,'(',1) as target_table, '##fkToNonUniqueKey' from (select rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition,
CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition, rc.REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS rc join information_schema.KEY_COLUMN_USAGE kc on rc.constraint_schema = kc.constraint_schema
AND rc.constraint_name = kc.constraint_name AND rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME is not NULL AND kc.REFERENCED_COLUMN_NAME is not NULL where (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) group by rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME, kc.referenced_table_schema) fk join (SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition, SUM(non_unique) as non_unique_count FROM
INFORMATION_SCHEMA.STATISTICS WHERE (table_schema NOT IN('sys','mysql','performance_schema','information_schema')) AND
sub_part IS NULL GROUP BY table_schema, table_name, index_name) idx on fk.target_fk_definition = idx.fk_definition AND idx.non_unique_count > 0;
+-------------------+--------------------+--+----------------+------------------------+--------------------+
| constraint_schema | constraint_name | | fk_definition | target_table | ##fkToNonUniqueKey |
+-------------------+--------------------+--+----------------+------------------------+--------------------+
| collection | collect_102_ibfk_1 | | collect_102(k) | collection.collect_101 | ##fkToNonUniqueKey |
| collection | collect_101_ibfk_1 | | collect_101(k) | sample.sample_101 | ##fkToNonUniqueKey |
+-------------------+--------------------+--+----------------+------------------------+--------------------+
2 rows in set (0.005 sec)
© 2026, Oracle Corporation and/or its affiliates
Description: In MySQL, we can have foreign keys referencing to different schema. Example: MySQL [(none)]> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, TABLE_NAME, REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS; +-------------------+--------------------+--------------------------+-------------+-----------------------+ | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_SCHEMA | TABLE_NAME | REFERENCED_TABLE_NAME | +-------------------+--------------------+--------------------------+-------------+-----------------------+ | collection | collect_101_ibfk_1 | sample | collect_101 | sample_101 | | collection | collect_102_ibfk_1 | collection | collect_102 | collect_101 | +-------------------+--------------------+--------------------------+-------------+-----------------------+ 2 rows in set (0.001 sec) When executing rechecks using mysqlsh with util check-for-server-upgrade, it executes "foreignKeyReferences" check which Checks for foreign keys not referencing a full unique index. This check only identifies foreign keys referenced to with in same schema and ignore foreign keys referencing to different schema. [root@testbox data]# mysqlsh -- util check-for-server-upgrade --user=root --host=localhost --password=**** --socket=/tmp/mysql_sandbox8042.sock --target-version=8.4.8 The MySQL server at /tmp%2Fmysql_sandbox8042.sock, version 8.0.42 - MySQL Community Server - GPL, will now be checked for compatibility issues for upgrade to MySQL 8.4.8. . . 4) Checks for foreign keys not referencing a full unique index (foreignKeyReferences) Foreign keys to partial indexes may be forbidden as of 8.4.0, this check identifies such cases to warn the user. collection.collect_102_ibfk_1 - invalid foreign key defined as 'collect_102(k)' references a non unique key at table 'collect_101'. Solutions: . . [root@testbox data]# When we check the actual query and the result clearly shows that foreign key referring to different schema excluded in the output: MySQL [(none)]> select fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition as fk_definition, fk.REFERENCED_TABLE_NAME as target_table, '##fkToNonUniqueKey' from (select rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition, CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition, rc.REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS rc join information_schema.KEY_COLUMN_USAGE kc on rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME is not NULL AND kc.REFERENCED_COLUMN_NAME is not NULL where (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) group by rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME) fk join (SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition, SUM(non_unique) as non_unique_count FROM INFORMATION_SCHEMA.STATISTICS WHERE (table_schema NOT IN('sys','mysql','performance_schema','information_schema')) AND sub_part IS NULL GROUP BY table_schema, table_name, index_name) idx on fk.target_fk_definition = idx.fk_definition AND idx.non_unique_count > 0; +-------------------+--------------------+--+----------------+--------------+--------------------+ | constraint_schema | constraint_name | | fk_definition | target_table | ##fkToNonUniqueKey | +-------------------+--------------------+--+----------------+--------------+--------------------+ | collection | collect_102_ibfk_1 | | collect_102(k) | collect_101 | ##fkToNonUniqueKey | +-------------------+--------------------+--+----------------+--------------+--------------------+ 1 row in set (0.004 sec) NOTE: Same issue exists for another query validating fkToPartialKey as well. SELECT constraint_schema, name, '', fk_definition, col_list, target_table, '##fkToPartialKey' FROM ( SELECT rc.constraint_schema constraint_schema, rc.constraint_name name, rc.referenced_table_name target_table, CONCAT(kc.table_schema,'.',rc.table_name,'(',GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') fk_definition, CONCAT(kc.referenced_table_schema,'.',kc.referenced_table_name,'(',GROUP_CONCAT(kc.referenced_column_name order by kc.ORDINAL_POSITION),')') col_list FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc ON rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.table_name = kc.table_name AND rc.referenced_table_name = kc.referenced_table_name AND (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) GROUP BY rc.constraint_name, rc.constraint_schema, kc.table_schema, kc.table_name, kc.referenced_table_schema, kc.referenced_table_name ) fk WHERE fk.col_list NOT IN ( SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') col_list FROM INFORMATION_SCHEMA.STATISTICS WHERE sub_part IS NULL AND (table_schema NOT IN('sys','mysql','performance_schema','information_schema')) GROUP BY table_schema, table_name, index_name ); How to repeat: ========= Create 8.0.42 instance and Create tables with below definition and then execute mysqlsh upgrade prechecks. CREATE DATABASE IF NOT EXISTS sample; CREATE DATABASE IF NOT EXISTS collection; CREATE TABLE IF NOT EXISTS `sample`.`sample_101` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE IF NOT EXISTS `collection`.`collect_101` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `blob_column` blob, PRIMARY KEY (`id`), KEY `k` (`k`), CONSTRAINT `collect_101_ibfk_1` FOREIGN KEY (`k`) REFERENCES `sample`.`sample_101` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE IF NOT EXISTS `collection`.`collect_102` ( `id` int NOT NULL, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `blob_column` blob, KEY `k` (`k`), CONSTRAINT `collect_102_ibfk_1` FOREIGN KEY (`k`) REFERENCES `collect_101` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Run mysql shell to upgrade to 8.4 mysqlsh -- util check-for-server-upgrade --user=root --host=localhost --password=***** --socket=/tmp/mysql_sandbox8042.sock --target-version=8.4.8 (OR) run below query select fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition as fk_definition, fk.REFERENCED_TABLE_NAME as target_table, '##fkToNonUniqueKey' from (select rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition, CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition, rc.REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS rc join information_schema.KEY_COLUMN_USAGE kc on rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME is not NULL AND kc.REFERENCED_COLUMN_NAME is not NULL where rc.constraint_schema not in ('mysql','information_schema','performance_schema','sys') group by rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME) fk join (SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition, SUM(non_unique) as non_unique_count FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema not in ('mysql','information_schema','performance_schema','sys') AND sub_part IS NULL GROUP BY table_schema, table_name, index_name) idx on fk.target_fk_definition = idx.fk_definition AND idx.non_unique_count > 0; Suggested fix: When we removed condition rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA and include kc.referenced_table_schema in the definition of derived table fk then the query works as expected: MySQL [(none)]> select fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition as fk_definition, fk.REFERENCED_TABLE_NAME as target_table, '##fkToNonUniqueKey' from (select rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition, CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition, rc.REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS rc join information_schema.KEY_COLUMN_USAGE kc on rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME is not NULL AND kc.REFERENCED_COLUMN_NAME is not NULL where (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) group by rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME, kc.referenced_table_schema) fk join (SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition, SUM(non_unique) as non_unique_count FROM INFORMATION_SCHEMA.STATISTICS WHERE (table_schema NOT IN('sys','mysql','performance_schema','information_schema')) AND sub_part IS NULL GROUP BY table_schema, table_name, index_name) idx on fk.target_fk_definition = idx.fk_definition AND idx.non_unique_count > 0; +-------------------+--------------------+--+----------------+--------------+--------------------+ | constraint_schema | constraint_name | | fk_definition | target_table | ##fkToNonUniqueKey | +-------------------+--------------------+--+----------------+--------------+--------------------+ | collection | collect_102_ibfk_1 | | collect_102(k) | collect_101 | ##fkToNonUniqueKey | | collection | collect_101_ibfk_1 | | collect_101(k) | sample_101 | ##fkToNonUniqueKey | +-------------------+--------------------+--+----------------+--------------+--------------------+ 2 rows in set (0.006 sec) To further enhance the output, we can include the schema name followed by table name in the target_table column listing. MySQL [(none)]> select fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition as fk_definition, substring_index(fk.target_fk_definition,'(',1) as target_table, '##fkToNonUniqueKey' from (select rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition, CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition, rc.REFERENCED_TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS rc join information_schema.KEY_COLUMN_USAGE kc on rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME is not NULL AND kc.REFERENCED_COLUMN_NAME is not NULL where (rc.constraint_schema NOT IN('sys','mysql','performance_schema','information_schema')) group by rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME, kc.referenced_table_schema) fk join (SELECT CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition, SUM(non_unique) as non_unique_count FROM INFORMATION_SCHEMA.STATISTICS WHERE (table_schema NOT IN('sys','mysql','performance_schema','information_schema')) AND sub_part IS NULL GROUP BY table_schema, table_name, index_name) idx on fk.target_fk_definition = idx.fk_definition AND idx.non_unique_count > 0; +-------------------+--------------------+--+----------------+------------------------+--------------------+ | constraint_schema | constraint_name | | fk_definition | target_table | ##fkToNonUniqueKey | +-------------------+--------------------+--+----------------+------------------------+--------------------+ | collection | collect_102_ibfk_1 | | collect_102(k) | collection.collect_101 | ##fkToNonUniqueKey | | collection | collect_101_ibfk_1 | | collect_101(k) | sample.sample_101 | ##fkToNonUniqueKey | +-------------------+--------------------+--+----------------+------------------------+--------------------+ 2 rows in set (0.005 sec)