June 11, 2009 at 4:16 am
Hi,
I have a database copy script which roughly works as: truncating data, bcp in new data.
I modified this script for a another database which has foreign keys. In order to prevent FK errors, I disable every FK in this database:
alter table {tablename} nocheck constraint {name}
However, truncating a table with even(!) disabled constraints, results in:
Cannot truncate table 'dbo.Activity' because it is being referenced by a FOREIGN KEY constraint.
Changing the truncate table in delete from results in no errors, but leads to a very large logfile. I didn't found any restrictions on truncate in combination with FK (in BOL).
- Is this a known issue (bug?)
- Is there a solution for this? (dropping the FK is not an option)
I use MSSQL 2005 EE SP3
Thanks!
Wilfred
The best things in life are the simple things
June 11, 2009 at 5:07 am
This isn't so much of an issue but a feature of the truncate command. Because truncate is not a fully logged operation it doesn't check for referential integrity so if there are any referencing tables the truncate command won't be allowed to run. Disabling the constraint is not enough - you must drop it.
Incidentally, why is dropping the constraint not an option?
June 11, 2009 at 5:57 am
Wilfred van Dijk (6/11/2009)
I didn't found any restrictions on truncate in combination with FK (in BOL).- Is this a known issue (bug?)
Not a bug, a documented restriction.
From Books online, the page titled "Truncate Table"
Restrictions
You cannot use TRUNCATE TABLE on tables that:
- Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
- Participate in an indexed view.
- Are published by using transactional replication or merge replication.
Either drop the constraint or use Delete.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply