我使用下面的表来实现子类型,这是一个很常见的方法：
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中的错误还是我错过了什么？
解决方法
看起来像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'
