Is this a bug in MERGE, failing to implement FOREIGN KEY properly?

Question

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');

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;

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.

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

It seems to be passing an empty string as `type`. If I remove the check constraint and add a row `INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, '');` Then the `Merge` works for `VALUES(3, 'Some Data'); ` — Martin Smith, Oct 14 '11 at 18:35
@JNK - Yes I didn't say it was correct behaviour (it seems decidedly incorrect), I just said that's what appears to be happening. — Martin Smith, Oct 14 '11 at 18:38
A workaround if you really want you use merge would be to have regular column type in cars with not null and a default constraint like this `[Type] varchar(5) not null default 'Car'` and also add a check constraint `CONSTRAINT Cars_CHK_ValidType CHECK([Type] = 'Car')`. I think that would add up to the same behavior as the persisted column and the merge works just fine. — Mikael Eriksson, Oct 14 '11 at 21:30
@MikaelEriksson: this is exactly what are using. What I really want is to have rock solid data integrity. I can and maybe should get rid of MERGE, as it seems to be not fully robust yet. I waited until R2 and thought that would be enough to have it reliable. Probably I was wrong. — A-K, Oct 15 '11 at 02:26
Open a connect item. While I'm not sure that 'persisted constant' should behave like a default constraint, I'm convinced the behavior of INSERT and MERGE should be consistent to each other (either both fail the insert or both succeed). — Remus Rusanu, Oct 15 '11 at 05:21

Martin Smith · Accepted Answer · 2011-10-14T19:20:20.233

Looks like a definite bug in MERGE to me.

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

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, '');

The following statement now works

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);

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

Cars

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

Vehicles

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

Checking the constraints immediately afterwards

DBCC CHECKCONSTRAINTS  ('dbo.Cars')

Shows the offending row

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

Is this a bug in MERGE, failing to implement FOREIGN KEY properly?

1 Answers1

Cars

Vehicles

Linked