Truncate tables with foreign keys

  • joeroshan

    SSChampion

    Points: 10377

    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]

  • ggrewe

    SSC Journeyman

    Points: 87

    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.

  • Robert Sterbal

    SSChampion

    Points: 10995

    How is the script licensed?

    412-977-3526 call/text

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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.

  • joeroshan

    SSChampion

    Points: 10377

    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]

  • joeroshan

    SSChampion

    Points: 10377

    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]

  • Robert Sterbal

    SSChampion

    Points: 10995

    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

  • joeroshan

    SSChampion

    Points: 10377

    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