Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TRUNCATE / Foreign Key issue Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 4:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:25 AM
Points: 929, Visits: 1,127
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
Post #732881
Posted Thursday, June 11, 2009 5:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:43 AM
Points: 870, Visits: 897
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?


Karl
source control for SQL Server
Post #732915
Posted Thursday, June 11, 2009 5:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #732954
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse