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 13668 14849 16090 18086 18441 21983 22421 22480 22658 25731 26044 26493 29390 12601 13007 13515 13518 13533 13534 13535 16170 17160 18050 21901 22368 22887 24289 28972
  Show dependency treegraph
Reported: 2010-11-01 12:40 UTC by Galen Charlton
Modified: 2021-11-02 12:29 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
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.