sql-server - 删除周围约束

Question

我必须创建一个 SQL 查询(由 SQL Server 中的 sqlcmd.exe 运行的 .sql 文件)来删除超过一定天数的所有记录。然而，我发现自己对如何绕过外键约束感到困惑。为了说明这个问题，这里有三个具有相似关系的表(注意这是伪代码):

CREATE TABLE runlog(
    row_id int identity(1,1) NOT NULL,
    run_id nvarchar(25) NULL FOREIGN KEY REFERENCES status(run_id),
    master_id nvarchar(25) NULL FOREIGN KEY REFERENCES master(master_id),
)

CREATE TABLE status(
    run_id nvarchar(25) NOT NULL,
    master_id nvarchar(25) NULL FOREIGN KEY REFERENCES master(master_id),
    status_date datetime NULL,
)

CREATE TABLE master(
    master_id nvarchar(25) NOT NULL,
)

通常按运行日志、状态、主控顺序执行删除操作——但我需要确定记录多长时间的字段在状态表中。所以我不能在逻辑上从主表之前的状态表中删除，但我也不能反过来做。对于运行日志表，我可以使用这个:

delete from runlog 
inner join status on status.run_id = runlog.run_id 
where status.status_date <= DATEADD(DAY, -30, GETDATE())

要选择我需要的 master_id，我可以使用:

select master_id from status 
where status.status_date <= DATEADD(DAY, -30, GETDATE())

然后，如果有办法缓存此列表，我可以使用它从状态中删除列表，然后从 master 中删除列表，但如果没有新的存储过程，我不知道该怎么做。有什么建议么？

Answer 1

您可以在删除语句上使用输出子句将删除的数据插入到表变量中 http://msdn.microsoft.com/en-us/library/ms177564.aspx

这应该让你按照你需要的顺序进行删除。这假设 run_id 是 Status 的 PK，master_id 是 master 的 PK。

BEGIN TRANSACTION;
BEGIN TRY
    declare @runIds Table (id nvarchar(25))
    declare @masterIds Table (id nvarchar(25))

    delete rl 
    OUTPUT DELETED.run_id into @runids
    from runlog as rl
    inner join StatusTbl on StatusTbl.run_id = rl.run_id 
    where StatusTbl.status_date <= DATEADD(DAY, -30, GETDATE())

    delete from StatusTbl 
    OUTPUT DELETED.master_id into @masterIds
    where run_id in (select id from @runIds)

    delete from MasterTbl 
    where master_id in (select id from @masterIds)
END TRY
BEGIN CATCH
    SELECT 
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

编辑

而且我知道您的场景纯粹是假设性的，但您也可以使用 MERGE 进行连接删除，我很确定建议这样做而不是进行连接样式更新/删除。我还发现编写 MERGE 语句给我带来了很多快乐:-)

MERGE runlog AS target
    USING (select run_id, status_date FROM StatusTbl where StatusTbl.status_date <= DATEADD(DAY, -30, GETDATE())) AS source ([run_id], [status_date])
    ON (target.run_id = source.[run_id])
    WHEN MATCHED THEN DELETE
    OUTPUT DELETED.run_id into @runids;