feat(mysql): Identify foreign keys created on non-unique columns

Older versions of MySQL allow creating foreign keys on non-unique indexes. This is disallowed by default on newer versions of MySQL. Need to identify these and call out as potential issue.

Script that will list the FKs that are created on Non-unique columns

SELECT
kcu.CONSTRAINT_SCHEMA AS 'Database',
kcu.TABLE_NAME AS 'Child Table',
kcu.COLUMN_NAME AS 'Child Foreign Key Column',
kcu.CONSTRAINT_NAME AS 'Foreign Key Name',
kcu.REFERENCED_TABLE_NAME AS 'Parent Table',
kcu.REFERENCED_COLUMN_NAME AS 'Parent Referenced Column'
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
-- Only look at actual foreign key relationships
WHERE
kcu.REFERENCED_TABLE_NAME IS NOT NULL
-- Filter out keys where the parent column is part of a Primary or Unique Key
AND NOT EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE parent_kcu
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON parent_kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND parent_kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
WHERE
tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
AND parent_kcu.TABLE_NAME = kcu.REFERENCED_TABLE_NAME
AND parent_kcu.COLUMN_NAME = kcu.REFERENCED_COLUMN_NAME
AND parent_kcu.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
)
ORDER BY
kcu.CONSTRAINT_SCHEMA,
kcu.TABLE_NAME;

Start by reviewing the supplied INFORMATION_SCHEMA query and how the database assessment handles MySQL checks. Done means identifying foreign keys whose referenced columns are not part of a primary or unique key and calling them out as potential issues.

Written by the indexing model from the issue text.