THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

"Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows

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".


Published Monday, October 17, 2011 1:15 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Antony said:

Wow, that's shocking! Does this also happen in 2008, or was this a bug introduced in 2008 R2 (not got a 2008 instance handy to try it out)?

Anyone know if this has been fixed in Denali?

October 17, 2011 2:41 PM
 

Alexander Kuznetsov said:

Antony,

I do not have any 2008 servers running, so I do not have an answer.

October 17, 2011 5:53 PM
 

Jason said:

I can repro this on 10.0.4000

However, your merge statement works as expected in 11.0.1440!

October 18, 2011 3:18 AM
 

Alejandro Mesa said:

Hi Alex,

I have tested both scripts, your and Martin, on SQL Server 2012 CTP3 and I couldn't reproduce the problems. Seems that both bugs have been fixed.

Microsoft SQL Server "Denali" (CTP3) - 11.0.1515.0 (X64)

Jul 11 2011 15:25:39

Copyright (c) Microsoft Corporation

Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Cheers,

AMB

October 18, 2011 4:31 PM
 

Mark Holmes said:

Connect was wrong to dismiss this so quickly for the reason specified, IMHO.  I can repro with separate transactions on SQL2008 (10.09.4064).

If it was fixed for 2012 CTP3, hopefully they plan to fix it in 2008 as well.  

October 19, 2011 10:15 AM
 

Alexander Kuznetsov said:

Jason, Alejandro, and Mark,

Thank you for your feedback.

October 23, 2011 8:05 PM
 

Naomi said:

Can you post a link to Connect item?

December 8, 2011 9:41 PM
 

Alexander Kuznetsov said:

Naomi,

I do not know if this thing is on Connect.

December 10, 2011 11:25 AM
 

Niikola said:

The problem of false negatives looks similar to a problem of foreign key over non-clustered index (see http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/merge-bug-when-foreign-key ).

February 11, 2012 4:25 AM
 

Alexander Kuznetsov said:

Niikola,

This is surely similar. Looks like we need to be very careful with this MERGE thing ;(.

Thanks for sharing!

February 11, 2012 12:29 PM
 

ElkoSteve said:

Hey Alex,

sorry to dig up an old issue, but I ran into a problem using a CTE with a MERGE where the WHEN NOT MATCHED THEN INSERT statement seems to violate a CHECK CONSTRAINT on the table the CTE is based on.  If that makes sense.  

This is on 2008 R2.  Does it sound like the same issue?

May 8, 2013 1:19 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement