1

I have a system with shared multitenancy, which means each table contains data for all tenants with a TenantId column to distinguish between them.

Provisioning a new tenant is quick and easy, however now I'm facing a challenge with deleting a single tenant.

Given that entities depend on each other for consistency, how do I delete a tenant easily from my database, while the system is in use by other tenants?

The system uses SQL Server 2008 R2, if that helps.

Dor Rotman
  • 1,511
  • 1
  • 15
  • 27

3 Answers3

1

If I got you right - this is the classical case for use of FOREIGN KEYS with ON CASCADE option. You only delete one record from master tenants table and due to proper chain of FKeys the system deletes related records or updates the reference columns with NULL or DEFAULT value

Sometimes will not work in cases where table references itself with DELETE ON CASCADE

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
1

As Oleg has pointed out FK with ON CASCADE option should help. But since you haven't shown us the schema, I am not very sure whether there is a possibility of system throwing an error saying "Introducing FOREIGN KEY constraint causes cycles or Multiple cascade paths". If you see this error then may be instead of CASCADE DELETE add a INSTEAD OF DELETE trigger to do the job.

CREATE TRIGGER dbo.Tenants_Delete 
ON dbo.Tenants
INSTEAD OF DELETE
AS
BEGIN;
    --Delete from the Child and Master table as per your need here.
    --Make use of the magic table DELETED
END;
vmvadivel
  • 1,041
  • 5
  • 7
0

Here's another approach: If deleting the tenant causes too much headache, you may be able to use a workaround.

Simply add a boolean column active to your tenant table. Then introduce a view that selects only the active tenants. Adjust your stored procedures to look up the data in this view rather than in the original tenant table.

Gruber
  • 4,478
  • 6
  • 47
  • 74