sql - 这个查询怎么会违反主键呢？

Question

假设我有一个空的 ProductLocalizations 表，其复合主键由 ProductId 和 LanguageCode 字段组成，我执行以下查询。

INSERT INTO dbo.ProductLocalizations (ProductId, LanguageCode, Name, Description) 
SELECT ProductId, LanguageCode1, Name1, Description1 FROM dbo.BulkProducts 
WHERE ProductId NOT IN (SELECT ProductId FROM dbo.ProductLocalizations) UNION 
SELECT ProductId, LanguageCode2, Name2, Description2 FROM dbo.BulkProducts
WHERE ProductId NOT IN (SELECT ProductId FROM dbo.ProductLocalizations) UNION 
SELECT ProductId, LanguageCode3, Name3, Description3 FROM dbo.BulkProducts
WHERE ProductId NOT IN (SELECT ProductId FROM dbo.ProductLocalizations) UNION 
SELECT ProductId, LanguageCode4, Name4, Description4 FROM dbo.BulkProducts
WHERE ProductId NOT IN (SELECT ProductId FROM dbo.ProductLocalizations) 

根据我(有限的)SQL 知识，由于查询包含

WHERE ProductId NOT IN(SELECT ProductId FROM dbo.ProductLocalizations)

子句，不应该有任何方法来插入重复的(无效的)记录。但是，执行此操作时我收到

Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_dbo.ProductLocalizations'. Cannot insert duplicate key in object 'dbo.ProductLocalizations'. The duplicate key value is (29977, de). The statement has been terminated.

我错过了什么？

Answer 1

您的表中有一个复合主键，它由 ProductId 和 LanguageCode 组成。您需要修改 WHERE 子句以使用 NOT EXISTS。此外，seeing as you're unpivoting the table, you may want to use CROSS APPLY to improve the speed :

INSERT INTO dbo.ProductLocalizations (ProductId, LanguageCode, Name, Description) 
SELECT
    bp.ProductId, t.LanguageCode, t.Name, t.Description
FROM dbo.BulkProducts bp
CROSS APPLY(
    VALUES  (LanguageCode1, Name1, Description1),
            (LanguageCode2, Name2, Description2),
            (LanguageCode3, Name3, Description3),
            (LanguageCode4, Name4, Description4)
) t(LanguageCode, Name, Description)
WHERE NOT EXISTS(
    SELECT 1
    FROM dbo.ProductLocalizations p
    WHERE
        p.ProductId = bp.ProductId
        AND p.LanguageCode = t.LanguageCode
)

---

如果你不能使用CROSS APPLY，这里是改进的UNION ALL版本:

INSERT INTO dbo.ProductLocalizations (ProductId, LanguageCode, Name, Description) 
SELECT * 
FROM (
    SELECT ProductId, LanguageCode1, Name1, Description1 FROM dbo.BulkProducts bp UNION ALL
    SELECT ProductId, LanguageCode2, Name2, Description2 FROM dbo.BulkProducts bp UNION ALL
    SELECT ProductId, LanguageCode3, Name3, Description3 FROM dbo.BulkProducts bp UNION ALL
    SELECT ProductId, LanguageCode4, Name4, Description4 FROM dbo.BulkProducts bp 
) t (ProductId, LanguageCode, Name, Description)
WHERE NOT EXISTS(
    SELECT 1
    FROM dbo.ProductLocalizations p
    WHERE
        p.ProductId = t.ProductId
        AND p.LanguageCode = t.LanguageCode
)