TRUNCATE / Foreign Key issue

• Wilfred van Dijk

  SSCrazy Eights

  Points: 9003

  More actions

  June 11, 2009 at 4:16 am

  #138595

  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

• SQLZ

  SSChampion

  Points: 12872

  More actions

  June 11, 2009 at 5:07 am

  #1007901

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 11, 2009 at 5:57 am

  #1007933

  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

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