Bug 32630

Summary: Don't delete ILL requests when patron is deleted
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: ILLAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact:
Severity: enhancement    
Priority: P5 - low CC: jeremy.evans, magnus, martin.renvoize, pedro.amorim, tomascohen
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21809
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22513
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21983
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36360
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36361
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:

Description Katrin Fischer 2023-01-13 14:38:00 UTC
There is a FK constraint, that makes it so that when a patron is deleted, the ILL requests linked to the account are deleted as well.

This can potentially cause a number of problems:
* ILL requests deleted could be pending, in shipment etc. You will have no audit track if there are any problems with transport etc.
* You won't be able to do reliable statistics on number of ILL requests etc. especially since adding a request is not even logged.


I propose that instead of removing the borrowernumber, we either
* leave it
* set it to NULL

Would love to get some input on this one!
Comment 1 Katrin Fischer 2023-01-13 14:38:23 UTC
illrequests:

[...]
  `orderid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `backend` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`illrequest_id`),
  UNIQUE KEY `illrequest_id` (`illrequest_id`),
  KEY `illrequests_bnfk` (`borrowernumber`),
  KEY `illrequests_bcfk_2` (`branchcode`),
  KEY `illrequests_safk` (`status_alias`),
  CONSTRAINT `illrequests_bcfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `illrequests_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `illrequests_safk` FOREIGN KEY (`status_alias`) REFERENCES `authorised_values` (`authorised_value`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Comment 2 Martin Renvoize 2023-06-06 07:01:33 UTC
I think 'SET NULL' is the right thing to do here.. if you want the audit trail for what deleted borrower we should follow the pattern and add a deleted_borrower_id field to the table and trigger a move at patron delete.