Menu
I'm trying to delete user's data and all it's related data that is located in different tables. All the tables have Foreign Keys but without cascade delete.
I investigated some options:
Are there any more smart option or other Techniques?
I'm using Microsoft SQL Server 2012 (SP1)
897
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.
Recursion is achieved by WITH statement, in SQL jargon called Common Table Expression (CTE). It allows to name the result and reference it within other queries sometime later.
A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results. FROM cte_name; Again, at the beginning of your CTE is the WITH clause.
A recursive query is one that is defined by a Union All with an initialization fullselect that seeds the recursion. The iterative fullselect contains a direct reference to itself in the FROM clause. There are additional restrictions as to what can be specified in the definition of a recursive query.
Those are the best and most efficient ones. For production queries I would use 2.
The only other ways I can think of would (IMO) only be suitable for quick and dirty removal of data in a test environment (avoiding the need to analyse the correct order)
WITH CHECK to avoid leaving the FKs in an untrusted state which means that all preserved data needs to be re-validated.DELETE statements on affected tables in arbitrary order and run the batch as many times as necessary until it succeeds with no FK errors.
182
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
