locked
What is "Best Practice" for Deleting Table Rows that a Foreign Key Constraint RRS feed

  • Question

  • We have to periodically refresh our test data. What is considered "Best Practice" for Deleting SQL Server Table Rows from a Table in which there is a foreign key constraint?

    Thanks for your review and am hopeful for a reply.

    Monday, October 15, 2018 7:37 PM

All replies

  • follow this  link

    https://www.mssqltips.com/sqlservertip/4059/script-to-delete-data-from-sql-server-tables-with-foreign-key-constraints/


    Ramesh Babu Vavilla MCTS,MSBI

    Monday, October 15, 2018 7:47 PM
  • Refreshing a table with FKs could potentially end up in referential integrity errors if the corresponding PK table is not refreshed along with the FK table. This is why I use the following direct approach when doing full table refreshes.

    1) Script out the FK from the table being refreshed and drop the FK. 

    2) Refresh the table from prod. Also, refresh the PK table whose FK was created on the first table. This way, both tables retain the referential integrity as it exists in prod. 

    3) Recreate the FK (scripted in #1) on the refreshed FK table.

    Make sure to do this during low or no activity period (in prod) otherwise, something might write to the tables while your copy is in progress and recreating FK would become challenging.

    Your mileage may vary but the above is a quick way if the application doesn't care about the PK table being refreshed along. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Monday, October 15, 2018 9:02 PM
  • Hi ITBobbyP,

    Thank you for your posting.

     

    The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. If you would like to delete the rows  in child table you can delete directly. If you would like to delete the rows in parent table , you should delete the correspond data in the child table.

     

    If you only have a  foreign key in your two table , you can manually delete the children first and then delete the parent. For more details , you can refer to this article : https://stackoverflow.com/questions/8251146/delete-data-with-foreign-key-in-sql-server-table

     

    If the table is referenced by multiple tables via foreign keys (FKs) and these multiple tables again are referenced by other tables via FKs. Please follow Ramesh's advice .

     

    Hope it can help you.

     

    Best regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 16, 2018 2:47 AM