TRUNCATE / Foreign Key issue

  • 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

  • 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?

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply