Bug 30414 - Inconsistent (duplicated) FK constraint name: aqbudgets.aqbudgetperiods_ibfk_1
Summary: Inconsistent (duplicated) FK constraint name: aqbudgets.aqbudgetperiods_ibfk_1
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low minor (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 30486
  Show dependency treegraph
 
Reported: 2022-03-31 12:20 UTC by Marcel de Rooy
Modified: 2022-04-07 12:42 UTC (History)
0 users

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.