'Delete all rows in all table that reference to user table with same where condition table (When Relation Not Cascade On Delete)

I want to delete all rows of all tables that reference the user table in SQL Server

User Table Name : security.User
User Table PK : UserId

My query for table

Delete From FKschema.FKTable where FKColumn not in (1,2)

How can I do this with one query for all tables?

sqlsql-server


Solution 1:[1]

When we set a foreignkey to a table, we also provide another attribute (in django it is on_delete=models.CASCADE) which means that if the parent is deleted delete the relation too.

E g,

CREATE TABLE inventory
    ( inventory_id INT PRIMARY KEY,
    CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)

   ON DELETE CASCADE // this is what you need
   );

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Irfan wani

Related Questions