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.
Table of Contents:
03:46 – Foreign keys change performance
04:12 – Let’s say we have no foreign key
04:41 – This query has a join
05:05 – The plan with no foreign key
05:34 – Add a foreign key relationship
05:54 – Run the same query
06:30 – Foreign key join elimination
06:38 – Try to add an invalid PostTypeId
07:08 – You can use NOCHECK
07:54 – Now can I add that row?
08:16 – But when I try to re-enable my untrusted foreign key…
08:29 – I get an error if I use WITH CHECK
09:29 – Does foreign key join elimination work now?
09:33 – I get an error if I use WITH CHECK
09:36 – Does foreign key join elimination work now?
09:37 – Nope, the foreign key isn’t trusted
09:51 – Delete the row that violates the key
10:11 – Checking keys needs heavy locks
11:42 – Join elimination may not happen
12:24 – What about cascading updates/deletes?
13:31 – Example syntax – cascading deletes
14:34 – Behind the scenes…
15:51 – Cascading deletes
17:03 – Do I always need to index foreign keys?
18:14 – Creating the foreign key
19:12 – Indexes on the child table can help
19:38 – Takeaways
20:24 – BrentOzar.com/go/Trust
Brent says: when you’re deciding whether or not you should include foreign keys in your database, this video will help.
13 Comments. Leave new
There’s a scenario I want to describe where we sometimes create foreign keys without checking them (leaving them untrusted) and we decide to accept that they’re untrusted.
The most common scenario is when we discover a missing foreign key. We want to add the foreign key (enabled) that should have been there the whole time. But we don’t want to pay the performance hit or concurrency hit required to check the existing data to make it trusted. The idea is that we’d like to start caring about referential integrity for new or modified data (which is still *some* kind of improvement over the status quo).
Oh that’s right, you can actually add the constraint with NO CHECK! I see your logic there– why let it get worse before you make it 100% right?
I know some folks are hesitant to do that because they’re concerned about the application failing, but I agree that it’s a question of the “lesser weevil” that will vary by environment, and I can totally see your trick being useful. Thanks!
That was a great refresher on FK’s. Thanks for sharing!
That was a really great refreshing video about FK. I already knew all those topics but sometimes is good to hear or read about that. And also is really amazing hear something interesting from a woman, that’s makes me think we are not alone among this it world.
Your English is perfect, I didn’t have any problem to understand you. Thanks for sharing greetings from México!!!!
I enabled the foreign key check and it is still doing a nested loop. Any suggestions?
It can’t always do foreign key elimination depending on the query / plan. That’s one of the reasons I list it as a perk / bonus. The main benefit to checking your constraints is knowing that the data is valid.
Very informative video. I have a question regarding indexing the foreign key. Is it sufficient to index the foreign key column only?
The scenario I’m thinking of is a typical Sales Invoice, Sales Invoice Line relationship. The sales invoice line will naturally have a foreign key to the sales invoice that it belongs to. In my system, all inserts, updates, and deletes at the line level will reference the primary key of the line. However, almost all select statements will have a where clause that references the line by the foreign key column to the sales invoice.
In this scenario, can the index on the foreign key be just against the SalesInvoiceId, or does it need to include all of the other columns that appear in the select list?
Hi Kevin,
If I’m reading correctly, I think your question is whether a nonclustered index on the child table should “Cover” all the columns in a select query. The answer does tend by vary by the queries and the data distribution. Like any other covering index, these can be quite large and bulky, and if you can get good performance with a narrower index you are often better off, but sometimes you do need to cover.
Sorry it’s not more of a clear yes/no.
Kendra
Hi Kendra,
Yes, that’s exactly what I was trying to ask, just framed in a much better way. I assumed the answer would be ‘it depends’, but I wanted to check.
I’m trying to avoid all of the overhead of having a clustered index on the primary key, and then also have a nonclustered index on the foreign key that also includes the rest of the columns in the table. If I understand this correctly, that will effectively double the storage requirement of that table.
Excellent video as always, but it reminded me of a nagging issue I have regarding non-trusted foreign keys.
I discovered about 1500 of these animals in my MS Dynamics 2011 database and would love to eliminate them but for the fact that the “is_not_for_replication” flag is set to 1. I notice in sp_blitz and the stand-alone script to check you’ve provided on the related post, that condition is specifically filtered out. I assume because the expectation is that it’s set that way for a reason. Funny thing is, I have no idea why so many of these tables are set “is_not_for_replication = 1” since I’m not replicating this database at all. I did replicate a staging database on which this version was built to support a data migration, but not nearly this many tables were replicated, so its a head-scratcher for sure. I could not find any posts about why MS would make this the out of the box setting for CRM, but I’m inclined to leave the default setting unless I’m compelled to change it.
So my questions are;
A.) Could I have inadvertently altered the keys of tables I never replicated by replicating others?
B.) If “A” is not likely, should I leave them alone and not attempt to eliminate the non-trusted keys and assume MS had a good reason for the setting? Changing the setting on the key would require dropping and recreating those keys which is an onerous task given the volume of data know living in said tables. I’m prepared to allow for the downtime it would take for the check, check, but it seems risky given I don’t know what other settings may need to be accounted for like “on delete cascade” for instance.
C.) If “B” is a good idea, is there a better way to update that setting to is_not_for_replication = 0 than dropping and recreating?
The “is not for replication” flag basically dictates how it will behave if the table is ever replicated. It does mess with the ‘trusted’/’untrusted’ status of the key.
I don’t think you should alter the schema of a Dynamics database. You could make future upgrades fail. You’re much better off leaving their design in place.
Wow, that was a super fast response Kendra. I see your Sundays are exciting as mine 🙂
I’m inclined to agree that one shouldn’t alter schema in third party software for the reason you mentioned. We are upgrading to Dynamics 2015 at some point soon, so maybe they will have fixed that anyway.
Thanks, you’re the best.
Haha, no problem at all. I just cleaned up my office and decorated it yesterday, so my excuse is that I’m enjoying the atmosphere in here 🙂