Post reply

Delete from parent table question

  • September 20, 2019 at 3:12 pm

    #3681944

    I have a parent table that is referenced by many other child tables . Is there a way to delete from this parent table with out dropping the foreign keys on other tables ?  Right now I am doing the following steps, which involves dropping a lot of foreign keys.

    exec sp_fkeys  MainTable

    --all the foreign keys are listed

    --dropping all the foreign keys

    --deleting from the parent table

    --recreating the foreign keys

    • This topic was modified 5 years, 3 months ago by  Guras.

  • September 20, 2019 at 3:27 pm

    #3681951

    you could set the foreign keys to cascade on delete

    then all of your child objects would be deleted when you delete the parent row

    MVDBA

  • September 20, 2019 at 3:29 pm

    #3681952

    I want to keep the child data but just clear out the parent  table.

  • September 20, 2019 at 3:31 pm

    #3681953

    so you are having to put the FK back using WITH NOCHECK?

    I can't really see why you have the foreign key in place (unless you are modifying their values to point to a different parent)

    MVDBA

  • September 20, 2019 at 4:10 pm

    #3681977

    Guras wrote:

    I want to keep the child data but just clear out the parent  table.

    In that case, I would do a "soft" delete.  That is, I would mark the parent record as being "deleted" without actually deleting it.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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