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
• Status: Verified
• Category: MySQL Enterprise Monitor: Advisors/Rules
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[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.