So what is it??
Just at that time I remembered that this table has a Child table in which we had records, but even if that is the case, ideally it should have given me an error, something like
“The DELETE statement conflicted with the REFERENCE constraint “FK_BillOfMaterials_Product_ComponentID”. The conflict occurred in database “AdventureWorks”, table “Production.BillOfMaterials”, column ‘ComponentID’.
The statement has been terminated.”
But it never gave any such error, we went ahead and deleted the Child table first and then came back to the parent and the error persisted! It was obvious that there should be some real reason for it to say it ran out of stack space, so we put on the thinking hats! Then we realized that the error happened because, this parent table has been referenced in a total of 395 tables across the database, because this is one of the real MASTER tables so when we run the Query the SQL Engine is trying to check if there are records in the children tables and as it has foreign key constraints across so many tables, it is NOT even able to query the tables and check the same!
So how did we solve? Now in our case we wanted to delete only a portion of records which we know that were inserted by our own Migration scripts and we knew we inserted related records ONLY in one of the Child tables, so we first deleted the records from this Child and then Disabled All the Foreign Key Constraints [changed CHECK to NOCHECK] at one go, [Oh! generating script for disabling ALL the FKs in a table is a separate tip, basically you make use of the sys.foreign_keys and the sys.tables tables] and then ran our first query again!
DELETE FROM AdventureWorks.Production.Product WHERE ProductID >= 1032
And Woila it deleted and no error now and then we Re-Enabled All the Foreign Key Constraints [changed NOCHECK to CHECK] at one go! Now, make sure to drop all the required children tables’ row first before you employ this trick if you ever come across this situation!
Happy SQLing!