[Koha-bugs] [Bug 5334] New: add explicit foreign key constraints

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5334

           Summary: add explicit foreign key constraints
 Change sponsored?: ---
           Product: Koha
           Version: HEAD
          Platform: All
        OS/Version: All
            Status: NEW
          Severity: enhancement
          Priority: P5
         Component: Database
        AssignedTo: gmcharlt at gmail.com
        ReportedBy: gmcharlt at gmail.com
         QAContact: koha-bugs at lists.koha-community.org
   Estimated Hours: 0.0


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.

-- 
Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA contact for the bug.