I have written on here about the benefits of Composable DML (i.e. the ability to insert the data outputted from an OUTPUT clause into a table) before however I discovered a limitation today that I think is worth sharing. Basically, the table that you’re inserting into cannot have a FOREIGN KEY constraint nor can it have a FOREIGN KEY constraint pointing to it. Here’s some code that demonstrates the first of these two restrictions:
/*This code demonstrates the fact that you can't use composable DML to insert into a table that has a FK*/
CREATE TABLE [TableWithPK] ([col1] INT PRIMARY KEY);
CREATE TABLE [TableWithFK] ([col1] INT REFERENCES [TableWithPK]([col1]));
/*Following statement fails with error:
Msg 356, Level 16, State 1, Line 17
The target table 'TableWithFK' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the
FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.*/
Definitely one to be aware of. Judging by this Connect submission: Insert over DML is not allowed on a table with a foreign key constraint this isn’t going to change any time soon either.
P.S. I have put the above code up on PasteBin too: http://pastebin.com/jk3q9P2e . I’ve started to use PasteBin more and more recently, take a look at http://pastebin.com/u/jamiet to see what else I have shared.