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

[Galen Charlton]

Hi,

On Tue, Apr 12, 2011 at 9:38 PM, Srdjan <srdjan at catalyst.net.nz> wrote:
> 1. issues table has nullable borrowernumber and itemnumber; foreign
> key constraints are consequently declared as "ON DELETE SET NULL ON
> UPDATE SET NULL"
>
> 2. That is not an isolated issue, there are many cases where foreign
> key field/constraints are declared like that
>
> Can anyone shed more light, that looksvery wrong to me.

And it is very wrong; this should be tightened up.  As to why?
Reasons best hidden in the mists of time, no doubt.

In the case of issues, I think it's pretty clear that both itemnumber
and borrowernumber should be non-NULL and have foreign key
constraints.  I suggest ON DELETE RESTRICT ON UPDATE CASCADE; if you
are deleting an item record or a patron, the application logic needs
to do the right thing if for whatever reason a library really wants to
delete a record that has an outstanding loan on it.

Regards,

Galen
-- 
Galen Charlton
gmcharlt at gmail.com