查询n秒内重复InspectionNotes及处理外键约束删除问题

嘿，这个外键约束的问题我之前也碰到过，刚好能给你几个实用的解决方案，你可以根据自己的业务场景来选：

方案一：先把关联记录指向保留的主记录，再删除重复项 #

如果那些关联的InspectionHoldDurations记录应该属于你要保留的那条InspectionNote（比如最早创建的那条），可以先更新关联表的引用，再删除重复的主表记录：

第一步：更新关联表，转移引用 #

WITH DuplicateNotes AS (
    SELECT 
        InspectionNoteID,
        -- 获取同组内要保留的记录ID（这里按创建时间最早的保留，你可以调整ORDER BY逻辑）
        FIRST_VALUE(InspectionNoteID) OVER (
            PARTITION BY InspectionID, Notes, UserRoleID  -- 这里是你判断重复的字段，按需调整
            ORDER BY CreateDate ASC
        ) AS KeepNoteID,
        ROW_NUMBER() OVER (
            PARTITION BY InspectionID, Notes, UserRoleID 
            ORDER BY CreateDate ASC
        ) AS rn
    FROM InspectionNotes
    WHERE CreateDate >= DATEADD(SECOND, -n, GETDATE()) -- 替换成你的n秒范围
)
UPDATE ihd
SET ihd.PutOnHoldNoteID = dn.KeepNoteID
FROM InspectionHoldDurations ihd
JOIN DuplicateNotes dn ON ihd.PutOnHoldNoteID = dn.InspectionNoteID
WHERE dn.rn > 1;

第二步：删除主表的重复记录 #

WITH DuplicateNotes AS (
    SELECT 
        InspectionNoteID,
        ROW_NUMBER() OVER (
            PARTITION BY InspectionID, Notes, UserRoleID 
            ORDER BY CreateDate ASC
        ) AS rn
    FROM InspectionNotes
    WHERE CreateDate >= DATEADD(SECOND, -n, GETDATE())
)
DELETE FROM DuplicateNotes WHERE rn > 1;

方案二：修改外键为级联删除（谨慎使用） #

如果业务逻辑允许删除InspectionNotes记录时自动删除关联的InspectionHoldDurations记录，可以修改外键约束添加级联删除：

先查看当前外键约束名称 #

SELECT name 
FROM sys.foreign_keys 
WHERE referenced_object_id = OBJECT_ID('InspectionNotes')
AND parent_object_id = OBJECT_ID('InspectionHoldDurations');

删除原外键并重新创建（替换成你的外键名称） #

-- 删除原外键
ALTER TABLE InspectionHoldDurations
DROP CONSTRAINT [FK_你的外键名称];

-- 重新创建带级联删除的外键
ALTER TABLE InspectionHoldDurations
ADD CONSTRAINT [FK_你的外键名称]
FOREIGN KEY (PutOnHoldNoteID)
REFERENCES InspectionNotes(InspectionNoteID)
ON DELETE CASCADE;

之后你再删除重复的InspectionNotes记录时，关联的InspectionHoldDurations记录会被自动删除。⚠️ 注意：这个操作会永久删除关联数据，一定要确认业务允许再执行！

方案三：先删除关联表的冗余记录，再删主表 #

如果那些关联的InspectionHoldDurations记录本身也是冗余的，不需要保留，可以先删关联表的对应行，再删主表重复项：

WITH DuplicateNotes AS (
    SELECT 
        InspectionNoteID,
        ROW_NUMBER() OVER (
            PARTITION BY InspectionID, Notes, UserRoleID 
            ORDER BY CreateDate ASC
        ) AS rn
    FROM InspectionNotes
    WHERE CreateDate >= DATEADD(SECOND, -n, GETDATE())
)
-- 先删除关联表中引用重复记录的行
DELETE ihd
FROM InspectionHoldDurations ihd
JOIN DuplicateNotes dn ON ihd.PutOnHoldNoteID = dn.InspectionNoteID
WHERE dn.rn > 1;

-- 再删除主表的重复记录
DELETE FROM DuplicateNotes WHERE rn > 1;

重要提醒 #

• 操作前一定要备份数据，或者在测试环境先验证所有逻辑！
• 确认你判断重复的规则（PARTITION BY的字段、n秒的范围）完全符合业务需求，避免误删正确的记录。

内容的提问来源于stack exchange，提问作者nerdperson