Truncate problems with Foreign Key Constraints
This one is a pretty simple one, but sometimes so am I, so I thought it'd be a good idea to document it and hopefully help some others out.
I recently ran into a problem when I was using the 'TRUNCATE TABLE ...' command on a table that contains foreign key references (using a SQL Server 2005 database). After a little reseach I found that when the table involves foreign key constaints, TRUNCATE will not work, however DELETE will.
The following article contain some technical explanations as to why this is the case: Truncate vs Delete.
In my case, I was using the TRUNCATE command in a test cleanup script, so I was not concerned about any database logging or altering identity values etc, so 'DELETE FROM TABLE ...' sorted it out. However I still try to use TRUNCATE where possible because of the lower overhead, but when it starts causing me unnecessary headaches I revert to DELETE.
The other question you'd probably asked yourself is why are you breaking the FK constraint?
If it's production data, that's almost a big no no.
Reply to this
The thing is I wasn't actually breaking any constraints - the 'TRUNCATE' statement would fail even when you took care in the order of deletion. ie. children first, then parents - so no orphans were left. It would even fail when you executed the command on a database with absolutely no data in it. DELETE on the other hand would only fail if you were trying to delete a parent that had children.
"If it's production data, that's almost a big no no."
I agree -if its a production system, handling data this way would be an issue, however this is only used in an isolated test environment.
The bigger issue at hand though is the fact that the reason I have this test cleanup script in the first place is because of a system that is tied too closely to the db - and therefore the datasource cannot be substituted for something else during automated testing.
Reply to this