sql-server – 这是MERGE中的错误,是否正确执行FOREIGN KEY？

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中的错误还是我错过了什么？

解决方法

执行计划具有聚簇索引合并运算符,并且应该输出[Cars] .ID,[Cars] .Type类型以对车辆表进行验证.

实验表明,代替将值“Car”作为Type值传递一个空字符串.这可以通过删除车辆上的检查约束来进行查看

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

以下声明现在有效

MERGE dbo.Cars AS TargetTable
    USING 
        ( SELECT    3 AS 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'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!