Bug 35611

Summary: Never use "ON UPDATE SET NULL"
Product: Koha Reporter: Jonathan Druart <jonathan.druart>
Component: Architecture, internals, and plumbingAssignee: Bugs List <koha-bugs>
Status: In Discussion --- QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: m.de.rooy, martin.renvoize, nick, tomascohen
Version: unspecified   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:

Description Jonathan Druart 2023-12-20 09:31:54 UTC
Why are we using "ON UPDATE SET NULL" in kohastructure.sql exactly? IMO it should be replaced with "ON UPDATE CASCADE", everywhere.
Comment 1 Martin Renvoize (ashimema) 2023-12-20 11:01:23 UTC
That does indeed seem strange to me... is it something around pseudoanonymisation perhaps?

Generally, I think I'd expect to set 'ON UPDATE CASCADE ON DELETE SET NULL'
Comment 2 Nick Clemens (kidclamp) 2023-12-20 12:18:18 UTC
What are the cases where we would change ids though? These all seem to reference rows that are auto increment, or cannot be edited (itemtype)

If you are changing one of these, is it the same object? Should the links between tables remain?

I am fine either way, but I don't think it should be happening very often
Comment 3 Marcel de Rooy 2023-12-20 12:20:02 UTC
(In reply to Jonathan Druart from comment #0)
> Why are we using "ON UPDATE SET NULL" in kohastructure.sql exactly? IMO it
> should be replaced with "ON UPDATE CASCADE", everywhere.

Not so sure about that. You might loose much more than you want to.
Comment 4 Marcel de Rooy 2023-12-20 12:21:01 UTC
(In reply to Marcel de Rooy from comment #3)
> (In reply to Jonathan Druart from comment #0)
> > Why are we using "ON UPDATE SET NULL" in kohastructure.sql exactly? IMO it
> > should be replaced with "ON UPDATE CASCADE", everywhere.
> 
> Not so sure about that. You might loose much more than you want to.

Hmm. Thats DELETE for sure ;)