Bug 30497

Summary: Constraint old_reserves_ibfk_4 should be SET NULL instead of CASCADE
Product: Koha Reporter: Marcel de Rooy <m.de.rooy>
Component: DatabaseAssignee: Marcel de Rooy <m.de.rooy>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: tomascohen
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00
Bug Depends on:    
Bug Blocks: 30486    
Attachments: Bug 30497: Recreate old_reserves_ibfk_4 when it cascades
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades
Bug 30497: Recreate old_reserves_ibfk_4 when it cascades

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 (kidclamp) 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!