Bug #60828 Add rule to detect duplicate foreign keys
Submitted: 11 Apr 2011 11:39 Modified: 11 Apr 2011 13:39
Reporter: Daniël van Eeden Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Enterprise Monitor: Advisors/Rules Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[11 Apr 2011 11:39] Daniël van Eeden
Description:
Duplicate Foreign Keys are allowed. (See Bug #13429)

It's unlikely that duplicate foreign keys are added on purpose, so a rule to detect this would be nice.

How to repeat:
Add duplicate constraints to a database monitored by merlin.

Suggested fix:
Use the following query as data source for this rule.

SELECT COUNT(*) AS NUMBER_OF_CONSTRAINTS,CONCAT(kcu.TABLE_SCHEMA,'.',kcu.TABLE_NAME,'.',kcu.COLUMN_NAME) AS COL,CONCAT(kcu.REFERENCED_TABLE_SCHEMA,'.',kcu.REFERENCED_TABLE_NAME,'.',kcu.REFERENCED_COLUMN_NAME) AS REF_COL,GROUP_CONCAT(kcu.CONSTRAINT_NAME) AS CONSTRAINTS,rc.UPDATE_RULE,rc.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE kcu LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc USING(CONSTRAINT_SCHEMA,CONSTRAINT_NAME) WHERE kcu.REFERENCED_TABLE_SCHEMA IS NOT NULL GROUP BY kcu.TABLE_SCHEMA,kcu.TABLE_NAME,kcu.COLUMN_NAME,kcu.REFERENCED_TABLE_SCHEMA,kcu.REFERENCED_TABLE_NAME,kcu.REFERENCED_COLUMN_NAME,rc.UPDATE_RULE,rc.DELETE_RULE HAVING COUNT(*) > 1\G
[11 Apr 2011 13:39] Valeriy Kravchuk
Thank you for the feature request.