Bug 30414

Summary: Inconsistent (duplicated) FK constraint name: aqbudgets.aqbudgetperiods_ibfk_1
Product: Koha Reporter: Marcel de Rooy <m.de.rooy>
Component: DatabaseAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: minor    
Priority: P5 - low    
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 30486    

Description Marcel de Rooy 2022-03-31 12:20:04 UTC
CREATE TABLE `aqbudgets` (
[etc]
  CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Normally, our constraints have the prefix showing the source table not the destination (aqbudgetperiods) like here.

DBrev20.06.00.055 contained this:
    if ( !foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets ADD CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON UPDATE CASCADE ON DELETE CASCADE
        |);

This created aqbudgetperiods_ibfk_1 next to aqbudgets_ifbk_1 that we already had in our production table.
Note that DBrev 3.01.00.077 already contained that one:
ALTER TABLE `aqbudgets`
   ADD CONSTRAINT `aqbudgets_ifbk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE

Conclusion: Older Koha database may contain a duplicated FK constraint.