Truncate tables with foreign keys

  • Comments posted to this topic are about the item Truncate tables with foreign keys

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I am not sure why are you dropping and recreating the Foreign Keys? Why not just disable and re-eanble them?

    If the table has an Identity column, you cannot Truncate the table, you must perform a delete and reseed the Identity column.

  • How is the script licensed?

    412-977-3526 call/text

  • ggrewe (12/1/2016)


    I am not sure why are you dropping and recreating the Foreign Keys? Why not just disable and re-eanble them?

    If the table has an Identity column, you cannot Truncate the table, you must perform a delete and reseed the Identity column.

    Ran into that issue about the Identity column myself.

  • ggrewe (12/1/2016)


    I am not sure why are you dropping and recreating the Foreign Keys? Why not just disable and re-eanble them?

    If the table has an Identity column, you cannot Truncate the table, you must perform a delete and reseed the Identity column.

    Truncate is not allowed if you have foreign keys defined even if they are disabled. But it is by design and to maintain the data integrity and it might be so because since it is a page level operation. But sometimes for large tables clean up, you might want to go for truncate instead of delete. But again, we should be very careful to avoid any data loss/ integrity issues

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • robert.sterbal 56890 (12/1/2016)


    How is the script licensed?

    Hi Robert

    I do not have any problem someone using the script to help their job done easy. In fact that is the point of publishing it no?

    If you are asking about republishing, please check with SSC for guidelines.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • joeroshan (12/2/2016)


    robert.sterbal 56890 (12/1/2016)


    How is the script licensed?

    Hi Robert

    I do not have any problem someone using the script to help their job done easy. In fact that is the point of publishing it no?

    If you are asking about republishing, please check with SSC for guidelines.

    The guidelines are that they have licensed the content from you.

    Your content may or may not be copyrighted. I'm asking for your permission to use it in any case.

    I always forget about the identity column issue, is there a way of addressing it?

    412-977-3526 call/text

  • robert.sterbal 56890 (12/2/2016)


    joeroshan (12/2/2016)


    robert.sterbal 56890 (12/1/2016)


    How is the script licensed?

    Hi Robert

    I do not have any problem someone using the script to help their job done easy. In fact that is the point of publishing it no?

    If you are asking about republishing, please check with SSC for guidelines.

    The guidelines are that they have licensed the content from you.

    Your content may or may not be copyrighted. I'm asking for your permission to use it in any case.

    I always forget about the identity column issue, is there a way of addressing it?

    You have my permission to use it, but with no warranties 😀

    I am not clear on your question on identity columns. It is possible to truncate a table with identity column(and no FKs). But the behaviour is different between truncate and delete. Truncate will reset the identity value but delete do not. So if you want to retain the identity max value for the tables some reason , you have to use DBCC CHECKIDENT

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

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