January 3, 2025 at 7:10 am
Hello everyone,
I am about to start a data archiving project that will affect almost all tables in my SQL database. To facilitate this process,
I have created an archive database with the same schema as the main database but without including foreign keys to ease the insertion order.
My question is: What are the potential impacts of not including foreign keys (FK) in the archive database on data integrity and archive management?
Is this acceptable in the context of archiving?
Thank you in advance for your advice and help!
January 3, 2025 at 9:55 am
This was removed by the editor as SPAM
January 3, 2025 at 1:53 pm
Strictly talking INSERT, and nothing else. Assuming the data being inserted is coming from an identical structure. That structure does, in fact, have foreign keys in place. Those keys are using WITH CHECK. We take all this into account. Yeah, your data integrity should be fine. Performance for the INSERT will be better than if the foreign keys were there. Win!
Now... UPDATE? DELETE? SELECT? We can quickly see issues start to arise, especially SELECT. Foreign keys are taken into account by the optimizer and are part of several processes that determine the best execution plan for a given query. That also comes up with UPDATE and DELETE. Depending on your situation, you could be seriously negatively impacting performance by not having the FKs in place.
Additionally, if you have errors during your data migration process, it's quite possible that your data could end up in a situation where the integrity is completely compromised because now there are children without parents in some tables.
I'm not saying, in your situation, this will work out negatively. However, it could, and you need to take that into account. Yes, there absolutely is overhead to having foreign keys. And yet, there's a reason we have foreign keys and use foreign keys in relational databases. More often than not, the overhead is worth it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply