Hello!

So, on another blog a long time ago, I wrote a couple of posts about foreign keys and what step can be taken to suspend them when we are trying to load/delete data into them. There’s the elegant way and the brute force way.

Hang On…

“But aren’t they there to stop this sort of thing?!” I hear the sensible people cry. And yes, this is true. BUT! Say we are inserting static data into a database at the point of creation using a tool like SSDT, we may not care about what order the data is loaded in, just that it is in. So, with some justification we can drop foreign keys post deploy, load the data and re-create.

Trouble is that elegant solution is totally over-engineered for this. And brute force is just bad. So is there an easier way?

Come to find out yes, there is an easier way. In just two lines:

sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
--chaos
sp_MSForeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
GO

So the first line removes all checks, as well as foreign keys. The second one rectifies the first one. And in between those two lines all manner of chaos can be scripted.