Bug 30497 - Constraint old_reserves_ibfk_4 should be SET NULL instead of CASCADE
Summary: Constraint old_reserves_ibfk_4 should be SET NULL instead of CASCADE
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Marcel de Rooy
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 30486
  Show dependency treegraph
 
Reported: 2022-04-08 07:54 UTC by Marcel de Rooy
Modified: 2023-06-08 22:28 UTC (History)
1 user (show)

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


Attachments
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades (1.69 KB, patch)
2022-04-08 08:27 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades (1.72 KB, patch)
2022-08-23 14:18 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades (1.74 KB, patch)
2022-09-02 07:19 UTC, Nick Clemens
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Marcel de Rooy 2022-04-08 07:54:28 UTC
kohastructure defines it as SET NULL
but this upgrade 'decided' differently:
$DBversion = "3.23.00.055"; ALTER TABLE old_reserves ADD CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE;

I think we should go for SET NULL here.
Comment 1 Marcel de Rooy 2022-04-08 08:27:59 UTC
Created attachment 133106 [details] [review]
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades

Test plan:
Drop old_reserves_ibfk_4.
Add back with:
    alter table old_reserves ADD CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE;
Run dbrev. Check that constraint has been replaced by SET NULL.
Run dbrev again. No changes.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 2 Marcel de Rooy 2022-08-23 14:11:48 UTC
QAing my own patch, DROP CONSTRAINT is bad, should be DROP FOREIGN KEY
Comment 3 Marcel de Rooy 2022-08-23 14:18:26 UTC
Created attachment 139680 [details] [review]
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades

Test plan:
Drop old_reserves_ibfk_4.
Add back with:
    alter table old_reserves ADD CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE;
Run dbrev. Check that constraint has been replaced by SET NULL.
Run dbrev again. No changes.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
[EDIT 23-08-22 Replaced DROP CONSTRAINT]
Comment 4 Marcel de Rooy 2022-08-23 14:19:45 UTC
Taking this one also to the next level. Architectural, close to trivial.
Comment 5 Nick Clemens 2022-09-02 07:19:33 UTC
Created attachment 140073 [details] [review]
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades

Test plan:
Drop old_reserves_ibfk_4.
Add back with:
    alter table old_reserves ADD CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE;
Run dbrev. Check that constraint has been replaced by SET NULL.
Run dbrev again. No changes.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
[EDIT 23-08-22 Replaced DROP CONSTRAINT]

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 6 Tomás Cohen Arazi 2022-09-22 12:43:15 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!