sql-server - 这是 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 中的一个明确错误对我来说。

执行计划有 Clustered Index Merge运算符，应该输出 [Cars].ID,[Cars].Type用于验证 Vehicles table 。

实验表明，而不是传递值 "Car"如 Type value 它正在传递一个空字符串。这可以通过删除 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'