Foreign Key on_delete db Constraints Discussion
The add_foreign_key docs show all the various options for on_delete and others: https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key
Hi Collin
Great episode, as ever: thank you
My question is "Why bother with the database constraints?". If the Rails associations allow you to specify (eg) "has_many :things, dependent: :nullify", why add the complexity of replicating this at the database level?
I'm sure there is a good reason, but it'd be great to know what it is
@Terry S: Years ago Rails as a whole seemed to avoid DB constraints and put all the logic into the application (Rails model) layer (my personal view of the community at the time). The thinking was why have developers dive into the land of SQL when we can keep them in Ruby land, where things are much nicer.
There isn't anything blatantly wrong with that approach if you control your DB and app and you're the only one connecting to it, which is what many Rails apps started as. However, as you start growing your app and you have multiple services or apps talking to your DB, you find that they are not all consistent with enforcing data integrity. Having a DB shared among multiple apps is a very real-world use case for enforcing data integrity at the DB level, since you don't control all the code talking to your DB.
In addition to the practical aspect of multiple DBs, the DBs are fine-tuned for performance at a level that Rails and ActiveRecord are not. There are a lot of things you can do in raw SQL that are lighting fast compared to Rails and ActiveRecord, such as bulk operations, etc...Some of these bulk operations assume you have data integrity at the SQL DB level to avoid duplicate records, etc. That's because these bulk operations have huge savings by avoiding ActiveRecord for the most part and avoiding instantiating Ruby objects completely and executing a single SQL query (think commands like insert_all, upsert_all, etc).
These are 2 very good reasons that are applicable in real-world apps. If you're running a small app for an internal business, it may not be as big a deal, but it's always easier to put them there from the beginning to establish data integrity rather than try and fix it years down the road.
As a real-world case, in my day-job this last year I had a developer who wrote some ActiveRecord Rails commands that were doing data migrations against an app that would have taken 30 days to complete...it was essentially an n+1 against millions of records. I re-wrote it using bulk SQL commands and got it done in 3 hours. Yes, you see that correct...30 days to 3 hours. Rails developers need to learn SQL. Years ago I didn't hold to that as much, but as I've worked on larger and more enterprise apps, I've solidly concluded you need to know SQL for huge performance gains when it matters.