这是 MERGE 中的错误，未能正确实现 FOREIGN KEY 吗? [英] Is this a bug in MERGE, failing to implement FOREIGN KEY properly?

问题描述

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

I am using the following tables to implement subtypes, which is a very common approach:

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添加子行没有问题，如下:

I have no problem adding a child row via INSERT, as follows:

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

DELETE FROM dbo.Cars;

令人惊讶的是，MERGE 未能添加一个子行:

Surprisingly, MERGE fails to add one child row:

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 中的错误还是我遗漏了什么?

Is this a bug in MERGE or am I missing something?

推荐答案

在我看来，这确实是 MERGE 中的一个错误.

Looks like a definite bug in MERGE to me.

执行计划具有 Clustered Index Merge 运算符，并且应该输出 [Cars].ID,[Cars].Type 以针对 Vehicles 进行验证 表格.

The execution plan has the Clustered Index Merge operator and is supposed to output [Cars].ID,[Cars].Type for validation against the Vehicles table.

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

Experimentation shows that instead of passing the value "Car" as the Type value it is passing an empty string. This can be seen by removing the check constraint on Vehicles then inserting

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 约束的行.

But the end result is that it inserts a row violating the FK constraint.

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'

这篇关于这是 MERGE 中的错误，未能正确实现 FOREIGN KEY 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！