I know. You, dear reader, saw that title and you came in here because you're furious. You want foreign key relationships configured in all of your tables to prevent bad data from getting in.
But you gotta make sure to index them, too.
If you update a row without actually changing its contents, does it still hurt?
Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That's a great post, and you should read it.
FINALLY...
This is the last post I'll write about foreign keys for a while. Maybe ever.
Let's face it, most developers probably find them more annoying than useful, and if you didn't implement them when you first started designing your database, you're not likely to go back and start trying to add them in.
This week, we're all about foreign keys. So far, we set up the Stack Overflow database to get ready, then tried to set up relationships, and encountered cascading locking issues.
Dawn Of The Data
I don't know how long the recommendation to index your foreign keys has been a thing, but I generally find it useful to abide by, depending a bit on how they're used.
Legendreary
In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.
I didn't even get to the point where I wanted to test all those relationships.
This week, we're all about foreign keys. Yesterday's post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships.
You Had Two Jobs!
Let's say I wanted to implement Foreign Keys to do two things
If a user deletes their account, all of their badges, comments, and posts will also get deleted
If a user deletes their post, all of the comments and votes will also get deleted
In A Foreign Key, In A Foreign Table Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then...). The same goes for check constraints, too. Thankfully, things like Primary Keys and Unique Constraints are sort…
It's one of those fierce religious wars that divides families and tears loved ones apart. First, if two tables are related, should we tell SQL Server about it? If SQL Server knows that there's a relationship, then it can make better decisions when building execution plans. The problem is that SQL Server has to be…
Do foreign keys hurt the performance of inserts? Okay, we all know that foreign keys do require some work, but the crazy people of the internet have wildly varying guesses as to just how much work is involved. Estimates varied from "barely any" to "as much as 50% slower". I figured that you were going…
Learn the basics of Foreign Keys in SQL Server: trust, join elimination, cascading deletes, and indexing child keys. Limited on time? Check out the Table of Contents below.
https://www.youtube.com/watch?v=BkNOQgV5Zv8
