SQL - delete two primary keys by reference in join table

Question

I have 3 tables:

1. user --> primary key = user_id
2. account --> primary key = account_id
3. user_account_join_table --> foreign key = user_id, foreign key = account_id

How can I delete all associated entries from all 3 tables when I only know user_id? One user can have many accounts and I would like to delete user and all of the accounts with one SQL statement, knowing only user_id.

Answer 1

You can use cascading deletes, but if you won't use it, then use transactions to ensure integrity of deletions:

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

     declare @userId int = 1; --your userId
     declare @accountIds table (id int);

     insert into @accountIds
     select accountId from user_account where userId = @userId;

     delete from user_account where userId = @userId;
     delete from [user] where id = @userId;
     delete from account where id in (select id from @accountIds);


      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH