Bug 5334 - Omnibus: Add explicit foreign key constraints
Summary: Omnibus: Add explicit foreign key constraints
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Galen Charlton
QA Contact: Bugs List
Depends on: 7811 12555 13518 13535 13668 14849 16090 17160 18050 18441 21983 22421 22480 22658 12601 13007 13515 13533 13534 16170 22368
  Show dependency treegraph
Reported: 2010-11-01 12:40 UTC by Galen Charlton
Modified: 2019-04-28 23:32 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:


Note You need to log in before you can comment on or make changes to this bug.
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.