Post reply

truncate table and forign key constraints

  • August 16, 2011 at 1:42 pm

    #242296

    I have a list of tables that need to truncate and repopulate with data.

    But when truncating I got an error that it cannot be truncated because of foreign key constraints.

    I found even the foreign key table is empty - no data, I still got the same error.

    My question is is it true even the foreign key table is empty, the PK table it refered to cannot be truncated because of this?

    Thanks

  • August 16, 2011 at 1:47 pm

    #1369300

    If a foreign key constraint references a table, that table cannot be truncated. Doesn't matter if the other table is empty, doesn't even matter if the constraints are disabled. If they exist at all, no truncation.

    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

  • August 16, 2011 at 1:47 pm

    #1369301

    a foreign key always prevents truncation.

    to do the same thing it requires two separate steps.

    instead you have to delete, and then maybe reseed the identity

    DELETE FROM MyTable

    DBCC CHECKIDENT( [MyTable],RESEED,1) --reset the identity to a value of 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 16, 2011 at 1:54 pm

    #1369306

    Thanks for the answers, that helps a lot

Viewing 4 posts - 1 through 4 (of 4 total)

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