[Koha-devel] Issues table and foreign keys in general

[Paul Poulain]

Le 16/04/2011 20:41, Chris Cormack a écrit :
> I disagree, option 4 is to make the change suggested by Srdjan, in which
> the database will enforce integrity and make it impossible to delete an
> item that  is checked out (has a row in issues). This covers us from
> mistakes in code, and from people making mistakes in the database.
> DELETE CASCADE is dangerous and should be avoided in this instance. It
> means if we make a mistake anywhere deleting an item that is checked
> out, the mistake will be compounded as we now delete the row in issues. The set
> NULL is nearly as bad, as we now null the column and still have no idea
> what it was linked to.
This problem is a functionnal problem: it's impossible to have an item
that is checked out AND deleted.
So we must find the best way for librarians to deal with the case they
try to do this. I agree that today situation is wrong.

I'm sure librarians will say : "hey, if I delete the item, it means no
one has it anymore, why does not Koha just check-in the pending issue?"
It's the same when you check-out an item that is already checked out :
koha says "item is checked out for XXX, do the check-in before checking
out to YYY?"
I think we should do the same : "you're about to delete an item
checked-out to XXX, do you confirm the check-in before deleting the item?"
>> About
>>> 2. That is not an isolated issue, there are many cases where foreign
>>> key field/constraints are declared like that
>> I wrote many of them with a reason that I think needed. Please tell me
>> which you consider as a problem, i'll tell you :
>> * why I choose this option if it's me
>> * that I was not the author of this choice, the author made a mistake
>> (and if git blame show you i'm the author of a buggy/wrong choice, i'll
>> argue that someone stole my identity just for this mistake :D )
> I think the database should never do this, I think our code should do it if it
> is needed. The database should stop us deleting things that are referred
> to elsewhere. We should clean up those references before trying to
> delete. The database should be the failsafe for us making mistakes, not
> allow us to make bigger mistakes :)
I'm not sure I understand what you mean here, so I explain what I wanted
to achieve with the constraints. I just used DELETE CASCADE or SET NULL
where it's logical. Like, when you delete a auth_tag_structure you
delete the subfields.
I used SET NULL only when I thought it was relevant to keep the entries
(like anonymised issues)


-- 
Paul POULAIN
http://www.biblibre.com
Expert en Logiciels Libres pour l'info-doc
Tel : (33) 4 91 81 35 08