← Back to team overview

openstack team mailing list archive

Using Foreign Keys

 

Dear Developers,

While testing our SUSE OpenStack packages we hit a nasty bug and reported it 
as:  https://bugs.launchpad.net/keystone/+bug/972502

We found out that the underlying cause was a lack of referential integrity[1] 
using sqlite or mysql. When we tried to reproduce this issue on postgresql the 
usage of foreign keys greatly helped to find the cause.

In order to prevent further inconsistencies we created a patch that added more 
foreign keys: https://review.openstack.org/6216

One reviewer commented:
> i don't approve of adding foreign keys, and we should probably remove the
> existing ones (in UserTenantMembership and in Endpoint)

and on 
https://review.openstack.org/#patch,sidebyside,6216,3,keystone/identity/backends/sql.py
> we shouldn't be using foreign keys at all, they are a crutch that are not
> available everywhere

This was a surprising answer to us as the usage of the foreign keys revealed 
the inconsistency in the first place. So removing them elsewhere does in no 
way improve the situation, it even does not help for mysql and sqlite, as 
SQLAlchemy abstracts them away. We also found similar bugs elsewhere:

* https://bugs.launchpad.net/keystone/+bug/959294
* https://bugs.launchpad.net/keystone/+bug/973243
* https://bugs.launchpad.net/keystone/+bug/974199

In our point of view foreign keys should be used in all possible places. This 
would not harm any database that does not support them but helps all of us to 
find data inconsistencies and related bugs, which leads to faster development 
with fewer bugs.


What is your take on these things?
How would you take care of data consistency otherwise?


Thank you for your feedback,
  Berhard M. Wiedemann
  J. Daniel Schmidt

[1]: http://en.wikipedia.org/wiki/Referential_integrity

-- 
J. Daniel Schmidt <jdsn@xxxxxxx>         SUSE LINUX Products GmbH
Research & Development                   Maxfeldstr. 5
HRB 16746 (AG Nürnberg)                  D-90409 Nürnberg
GF:  Jeff Hawn, Jennifer Guild, Felix Imendörffer



Follow ups