We do not use foreign keys (2016)
github.com
We do not use foreign keys (2016)
1–10 of 337 posts
Re: We do not use foreign keys (2016)
#2The main criticism seems to be that the FK relationship makes migrating the referenced table difficult. But why not remove the FK with ALTER TABLE before the migration, migrate, and add the FK back again (which will catch any missing primary keys), preferably inside a transaction?
Re: We do not use foreign keys (2016)
#3[deleted]
Re: We do not use foreign keys (2016)
#4The main criticism seems to be that the FK relationship makes migrating the referenced table difficult. But why not remove the FK with ALTER TABLE before the migration, migrate, and add the FK back again (which will catch any missing primary keys), preferably inside a transaction?
[deleted]
Re: We do not use foreign keys (2016)
#5When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you.
As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have a performance cost. Yes, they make sharding more difficult. In my experience, at smaller scale the trade-offs are worth it. YMMV
Re: We do not use foreign keys (2016)
#6The main criticism seems to be that the FK relationship makes migrating the referenced table difficult. But why not remove the FK with ALTER TABLE before the migration, migrate, and add the FK back again (which will catch any missing primary keys), preferably inside a transaction?
>preferably inside a transaction?
MySQL does not support transactional DDL, unfortunately.
Re: We do not use foreign keys (2016)
#7[deleted]
Re: We do not use foreign keys (2016)
#8How would a Rails app be impacted by this? How would you create one-to-many, or many-to-many relationships?
I'm sorry if this question sounds silly, but I've never heard this line of reasoning before and I'm fascinated by it.
Re: We do not use foreign keys (2016)
#9There seems to be a certain mysql tinge to this value calculation. I use foreign keys and like it (I use Postgres). I do not encounter the problems the author does. I omit them when they are a problem for some reason. This is rare in routine work.
Re: We do not use foreign keys (2016)
#10Worked at a bank years ago. One of their DBAs eschewed FKs in production. Databases were designed with FKs which were enforced in Dev and QA. If your app survived testing (automated, QA team ... the entire gamut) without producing FK violation exceptions, your app could be promoted to production, where FKs were not enforced, making things pretty fast.
I feel like this was a stopgap on the way to eliminating FKs. I have no idea what his roadmap was because I wasn't in that group. But his process made for good thought fodder.