Dealing With Foreign Keys

Kenneth Fisher discusses foreign key constraints:

I have to be certain to delete from OrderDetail first, then Order (to maintain the RI) and then load Order first then OrderDetail. No big deal in this simple example, but what if I’m dealing with a dozen tables? Or I’m only re-loading the parent (Order)?

The easiest thing to do is to disable the foreign key, load your data, and then re-enable the foreign key. You might be tempted to skip that last step but don’t. RI is very important and in fact a trusted foreign key can be used by the optimizer to improve your query plan. It’s easy to say that the application doesn’t have bugs that cause problems with referential integrity. It’s not like you’re ever going insert an OrderDetail without an Order right? Unfortunately it’s far to easy for mistakes to happen. Maybe not in the application, it might be a mistake in an update meant to fix something else. Our job is to protect the data, and RI is an important part of that. So by all means disable a foreign key to help with a load but make sure you turn it back on when you are done.

If a foreign key constraint isn’t trusted, the optimizer won’t be able to assume relational integrity, and so it’s possible that the optimizer could make sub-par choices when joining tables with a foreign key constraint.

Related Posts

Trigger Or Constraint?

Andy Levy points out that you shouldn’t use a trigger when a default constraint will do: We want to spend our SQL Server licensing dollars wisely, so why ask it to do unnecessary work? More CPU time, more IO, and our client is waiting longer for the process to complete (I don’t like making anyone wait). There’s […]

Read More

Constraints On Temp Tables

Kenneth Fisher argues that you should use default naming for temp table constraints: You should be able to create a #temp in every session. That’s the idea, right? It’s one of the things that differentiates a global temp table from a local temp table. But there can be some difficulties with that. If you are working with reusable […]

Read More

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930