-1

I have 3 tables like this:

  • Table1: Id, Name
  • Table2: Id, Name
  • Table3: Id, EntityName (nvarchar), EntityId (int)

There are no foreign keys on Table3. I am inserting rows for Table1 and Table2 to Table3 like this:

Id EntityName EntityId OtherColumn(s)
1 Table1 1 ...
2 Table2 1 ...
3 Table2 2 ...

When I delete a row from Table1 or Table2, how can I cascade it from Table3?

YSFKBDY
  • 735
  • 1
  • 12
  • 38

2 Answers2

1

This is how you could write queries to "mimic" cascade delete:

delete from table2 t2
where exists(select 1 from table3
             where t2.id = entityId and EntityName = 'Table2')

delete from table1 t1
where exists(select 1 from table3
             where t1.id = entityId and EntityName = 'Table1')

If you have some condition upon which you delete from table3 you should also inlcude it in those queries.

UPDATE

To do it automatically, you need todefine foreign keys with cascade delete action. But only one foreign key can be defined on column, thus you'd need to have two columns - one for referencing table1 and second for referncing table2. Then on both you'd need define cascade delete.

With current design (one column to reference multiple tables) this isn't possible and you would need to work around (as suggested implement delete trigger on table).

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
1

You can create a delete trigger on each table to delete the matching rows from table3, for example:

create trigger Table1Delete on dbo.table1
for delete 
as
if @@RowCount = 0 return
set nocount on

delete from t3
from deleted d
join table3 t3 on t3.EntityName='Table1' and d.Id=t3.EntityId

And a similar one for Table2

Stu
  • 30,392
  • 6
  • 14
  • 33
  • I was focusing on cascade delete so much, I've completely forgot triggers. – YSFKBDY Nov 06 '21 at 14:25
  • Aside: Rather than `@@RowCount = 0` I would use `not exists ( select 42 from deleted )`. It avoids any confusion that might result from a statement being inserted before the check or when the trigger is fired as the result of a [`merge`](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15#remarks) statement. – HABO Nov 07 '21 at 03:52
  • Using `@@rowcount` is from the documentation and what Microsoft recommends; although no longer the case in later versions, hitting the inserted/deleted tables in older versions was a costly operation to be minimised. – Stu Nov 07 '21 at 09:23