SQLServer Cascading Delete
ON DELETE CASCADE
Assume you have a application that administers rooms.
Assume further that your application operates on a per client basis (tenant).
You have several clients.
So your database will contain one table for clients, and one for rooms.
Now, every client has N rooms.
This should mean that you have a foreign key on your room table, referencing the client table.
Assuming a client moves on to some other software, you'll have to delete his data in your software. But if you do
Then you'll get a foreign key violation, because you can't delete the client when he still has rooms.
Now you'd have write code in your application that deletes the client's rooms before it deletes the client. Assume further that in the future, many more foreign key dependencies will be added in your database, because your application's functionality expands. Horrible. For every modification in your database, you'll have to adapt your
application's code in N places. Possibly you'll have to adapt code in other applications as well (e.g. interfaces to
There is a better solution than doing it in your code.
You can just add ON DELETE CASCADE to your foreign key.
Now you can say
and the rooms are automagically deleted when the client is deleted.
Problem solved - with no application code changes.
One word of caution: In Microsoft SQL-Server, this won't work if you have a table that references itselfs. So if you try to define a delete cascade on a recursive tree structure, like this:
it won't work, because Microsoft-SQL-server doesn't allow you to set a foreign key with ON DELETE CASCADE on a
recursive tree structure. One reason for this is, that the tree is possibly cyclic, and that would possibly lead to a deadlock.
PostgreSQL on the other hand can do this;
the requirement is that the tree is non-cyclic.
If the tree is cyclic, you'll get a runtime error.
In that case, you'll just have to implement the delete function yourselfs.
A word of caution:
This means you can't simply delete and re-insert the client table anymore, because if you do this, it will delete all entries in "T_Room"... (no non-delta updates anymore)