0

If I want to delete 1 person in "AdventureWorks2017" data base, for example:

DELETE FROM [Person].[Person] WHERE BusinessEntityID = 1

I know I have to make sure that all dependencies to that person are removed before I can do it.

Because Person.Person has many dependencies and may have 7 levels of dependencies (like for [Sales].[SalesOrderHeaderSalesReason]), what would be the best way to do it?

How do i write the db script to delete it and all that person’s dependencies.

Michael Bruesch
  • 632
  • 7
  • 23
Kaly Web
  • 1
  • 5
    Using cascading delete constraints. The database will do this automatically. – Gordon Linoff Oct 08 '18 at 15:06
  • 1
    Possible duplicate of [How to delete all dependent rows](https://stackoverflow.com/questions/28317894/how-to-delete-all-dependent-rows) – Marta B Oct 08 '18 at 15:14
  • See https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server – SUMguy Oct 08 '18 at 15:19
  • either with an `instead of trigger` or by setting `cascade delete` on – GuidoG Oct 08 '18 at 15:43
  • 1
    "Best way"? I'm a fan of putting that sort of logic in a stored procedure. It can delete all of the related rows within the context of a single transaction. It allows you to revoke the permissions that allow users to delete rows directly from the tables involved so that they must use your SP, provides a place to do additional authorization checks, logging, ... . Mistakes can propagate with remarkable speed when a cascade is triggered. – HABO Oct 08 '18 at 16:06
  • Possible duplicate of [How do I use cascade delete with SQL Server?](https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server) – 3N1GM4 Oct 10 '18 at 15:19

0 Answers0