这是 MERGE 中的错误，未能正确实现 FOREIGN KEY 吗？

Question

我使用下表来实现子类型，这是一种非常常见的方法：

CREATE TABLE dbo.Vehicles(
    ID INT NOT NULL, 
    [Type] VARCHAR(5) NOT NULL,
    CONSTRAINT Vehicles_PK PRIMARY KEY(ID),
    CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE(ID, [Type]),
    CONSTRAINT Vehicles_CHK_ValidTypes CHECK([Type] IN ('Car', 'Truck'))
);
GO

CREATE TABLE dbo.Cars(ID INT NOT NULL,
    [Type] AS CAST('Car' AS VARCHAR(5)) PERSISTED,
    OtherData VARCHAR(10) NULL,
    CONSTRAINT Cars_PK PRIMARY KEY(ID),
    CONSTRAINT Cars_FK_Vehicles FOREIGN KEY(ID, [Type])
        REFERENCES dbo.Vehicles(ID, [Type])
);
GO
-- adding parent rows
INSERT INTO dbo.Vehicles(ID, [Type]) 
VALUES(1, 'Car'),
(2, 'Truck');

我通过 INSERT 添加子行没有问题，如下所示：

INSERT INTO dbo.Cars(ID, OtherData)
VALUES(1, 'Some Data');

DELETE FROM dbo.Cars;

令人惊讶的是，MERGE 无法添加一个子行：

MERGE dbo.Cars AS TargetTable
    USING 
        ( SELECT    1 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED 
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);

Msg 547, Level 16, State 0, Line 1
The MERGE statement conflicted with the FOREIGN KEY constraint "Cars_FK_Vehicles". The conflict occurred in database "Test", table "dbo.Vehicles".
The statement has been terminated.

这是 MERGE 中的错误还是我缺少什么吗？

Answer 1

对我来说，这看起来像是 MERGE 中的一个明确的错误。

执行计划具有聚集索引合并运算符，并且应该输出[Cars].ID,[Cars].Type以针对Vehicles进行验证> 表。

实验表明，传递的不是 "Car" 作为 Type 值，而是传递一个空字符串。这可以通过删除 Vehicles 上的检查约束然后插入来看出。

INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, '');

以下语句现在可以工作

MERGE dbo.Cars AS TargetTable
    USING 
        ( SELECT    3 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED 
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);

，但最终结果是它插入了违反 FK 约束的行。

汽车

ID          Type  OtherData
----------- ----- ----------
3           Car   Some Data

车辆

ID          Type
----------- -----
1           Car
2           Truck
3           

随后立即检查约束

DBCC CHECKCONSTRAINTS  ('dbo.Cars')

显示有问题的行

Table         Constraint          Where
------------- ------------------- ------------------------------
[dbo].[Cars]  [Cars_FK_Vehicles]  [ID] = '3' AND [Type] = 'Car'