Bug 5334

Summary:	Omnibus: Add explicit foreign key constraints
Product:	Koha	Reporter:	Galen Charlton <gmcharlt>
Component:	Database	Assignee:	Galen Charlton <gmcharlt>
Status:	NEW ---	QA Contact:	Bugs List <koha-bugs>
Severity:	enhancement
Priority:	P5 - low	CC:	jonathan.druart, martin.renvoize
Version:	Main
Hardware:	All
OS:	All
See Also:	https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13484 https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13533 https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14818
Change sponsored?:	---	Patch complexity:	---
Documentation contact:		Documentation submission:
Text to go in the release notes:		Version(s) released in:
Circulation function:
Bug Depends on:	12555, 13668, 14849, 16090, 22421, 22658, 26044, 26493, 29390, 38011, 7811, 12601, 13007, 13515, 13518, 13533, 13534, 13535, 16170, 17160, 18050, 18086, 18441, 21901, 21983, 22368, 22480, 22887, 24289, 25731, 28972, 35610
Bug Blocks:

Description Galen Charlton 2010-11-01 12:40:41 UTC

There are many tables that lack explicit foreign key relationships to parent tables.  For example:

* serial (biblionumber, subscriptionid)
* subscription (biblionumber)
* subscriptionhistory (biblionumber, subscriptionid)
* suggestions (suggestedby, managedby, rejectedby)
* branch_transfer_limits (toBranch, fromBranch)

and many, many others.

Explicit foreign keys should be added for several reasons:

* to promote better data integrity and to expose cases where the application code makes unwarranted assumptions about the database structure
* to allow cascading updates and deletes in some cases
* to make it easier for external reporting tools to correctly identify relationships between tables
* to make use of an ORM more effective

There are some arguments against this:

* a complete set of FKs may decrease database speed (on the other hand, the Koha schema already uses a bunch of FKs)

There are some implementation details to consider:

* the database creation scripts may need some reordering
* in some cases, a database update to add an FK may require dealing with rows that do not meet the new constraint.

Comment 1 Jonathan Druart 2014-07-18 10:29:14 UTC

And aqorders.budget_id, see bug 12601.

Comment 2 Jonathan Druart 2014-08-20 10:07:08 UTC

Bug 12555 is going to add a FK on aqorders_items.itemnumber.