SQL-通过引用删除联接表中的两个主键

Question

我有3个表：

1. 用户-->主键=user_id
2. 帐户-->主键=account_id
3. user_account_join_table-->外键=user_id，外键=account_id

当我只知道user_id时，如何从所有3个表中删除所有关联条目？一个用户可以有多个帐户，我想删除用户和所有帐户只有一个SQL语句，只知道user_id。

Answer 1

您可以使用cascading deletes，但如果不使用，则使用事务来确保删除的完整性：

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