In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save.
False Negatives
The following tables implement a very common type/subtype pattern:
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');
The following INSERT succeeds:
INSERT INTO dbo.Cars(ID, OtherData)
VALUES(1, 'Some Data');
DELETE FROM dbo.Cars;
MERGE should be able to add the same row, but it fails:
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.
False Positives
Martin Smith has posted this repro on stackoverflow, in response to my scenario described above. This script successfully adds an orphan row into dbo.Cars table:
ALTER TABLE dbo.Vehicles
DROP CONSTRAINT Vehicles_CHK_ValidTypes ;
GO
INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, '');
GO
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);
GO
-- the FK constraint is enabled and trusted
SELECT is_disabled,is_not_trusted
FROM sys.foreign_keys
WHERE name = 'Cars_FK_Vehicles';
-- yet we have an orphan row
SELECT * FROM dbo.Cars
SELECT * FROM dbo.Vehicles
GO
is_disabled is_not_trusted
----------- --------------
0 0
(1 row(s) affected)
ID TYPE OtherData
----------- ----- ----------
3 Car SOME Data
(1 row(s) affected)
ID TYPE
----------- -----
1 Car
2 Truck
3
Conclusion
In Ch 7 of my book "Defensive Database Programming", I wrote the following statement.
"As long as a constraint is trusted, then we know for sure that all our data complies with the rules that it dictates."
At the time I was writing that, I believed that was true. Unfortunately, as we have just seen, I was wrong - we can bypass trusted foreign key constraints in SQL 2008 R2.
P.S.
I have just learned that there is another scenario to bypass a foreign key. Google up this: "Parameterized DELETE and MERGE Allow Foreign Key Constraint Violations".
Another very important thing that I have just learned that this Connect item did not get urgent priority. Quoting directly from Connect: "Since this is not a common scenario or use case i.e., doing DDL changes
and DML in the same transaction we don't see this as a big issue".