规避外键引用后删除重复记录仍触发REFERENCE约束冲突

原SQL逻辑存在漏洞，导致筛选出的记录仍包含被tblPriceDetail引用的行，触发外键约束错误：

• 用LEFT JOIN关联后，当tblPriceDetail无匹配引用记录时，pd.EventHeaderRecID为NULL，此时ph.RecID != pd.EventHeaderRecID的结果是UNKNOWN，不会被纳入筛选范围，真正未被引用的重复记录反而没被选中。
• 若某个EventID下有部分表头被引用，原SQL会错误选中那些与引用记录RecID不匹配的表头（但这些表头可能本身也被其他tblPriceDetail记录引用）。

核心思路是先确定每个重复EventID中需要保留的记录（优先保留被引用的，无引用则保留任意一条，比如最小RecID），再删除其余重复项。

方案1：直接删除未被引用的重复表头 #

适用于tblPriceDetail中没有引用待删除表头的场景：

DELETE FROM tblPriceHeader
WHERE rowguid IN (
    SELECT ph.rowguid
    FROM tblPriceHeader ph
    -- 关联每个EventID下需要保留的RecID：优先取被引用的，无引用则取最小RecID
    LEFT JOIN (
        SELECT DISTINCT EventHeaderRecID AS KeepRecID, EventID
        FROM tblPriceDetail
    ) keep ON ph.EventID = keep.EventID
    WHERE ph.EventID IN (
        SELECT EventID
        FROM tblPriceHeader
        GROUP BY EventID
        HAVING COUNT(EventID) > 1
    )
    -- 删除条件：当前RecID不是需要保留的记录
    AND ph.RecID != COALESCE(keep.KeepRecID, (
        SELECT MIN(RecID) FROM tblPriceHeader WHERE EventID = ph.EventID
    ))
)

方案2：先迁移引用再删除表头 #

若tblPriceDetail有记录引用待删除的表头，需先将这些详情的外键指向保留的表头，再删除重复项：

-- 第一步：把引用重复表头的详情记录，改为指向要保留的表头
UPDATE tblPriceDetail pd
SET EventHeaderRecID = (
    SELECT COALESCE(
        -- 优先取同EventID下被引用的RecID，无则取最小RecID
        (SELECT DISTINCT TOP 1 EventHeaderRecID FROM tblPriceDetail WHERE EventID = pd.EventID),
        (SELECT MIN(RecID) FROM tblPriceHeader WHERE EventID = pd.EventID)
    )
)
WHERE pd.EventHeaderRecID IN (
    SELECT ph.RecID
    FROM tblPriceHeader ph
    WHERE ph.EventID IN (
        SELECT EventID FROM tblPriceHeader GROUP BY EventID HAVING COUNT(EventID) > 1
    )
    AND ph.RecID != COALESCE(
        (SELECT DISTINCT TOP 1 EventHeaderRecID FROM tblPriceDetail WHERE EventID = ph.EventID),
        (SELECT MIN(RecID) FROM tblPriceHeader WHERE EventID = ph.EventID)
    )
)

-- 第二步：删除重复的表头记录
DELETE FROM tblPriceHeader
WHERE rowguid IN (
    SELECT ph.rowguid
    FROM tblPriceHeader ph
    WHERE ph.EventID IN (
        SELECT EventID FROM tblPriceHeader GROUP BY EventID HAVING COUNT(EventID) > 1
    )
    AND ph.RecID != COALESCE(
        (SELECT DISTINCT TOP 1 EventHeaderRecID FROM tblPriceDetail WHERE EventID = ph.EventID),
        (SELECT MIN(RecID) FROM tblPriceHeader WHERE EventID = ph.EventID)
    )
)

• COALESCE用于兼容两种场景：当EventID有被引用的表头时，保留被引用的；无引用时，按规则保留一条（示例用最小RecID，可替换为MAX(CreatedDate)等自定义规则）。

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