This is a follow up post of
In the above post I’ve illustrated how Composable DML feature can be applied for simultaneous insert to parent child tables. I was trying to use this feature in a similar scenario when I discovered something. The scenario can be depicted as below
CREATE TABLE Parent
(
ID int IDENTITY(1,1) NOT NULL,
Value1 varchar(100)
)
CREATE TABLE Child
( ID int IDENTITY(1,1) NOT NULL,
Parent_ID int REFERENCES Parent(ID),
Value2 varchar(100),
Value3 varchar(100)
)
Now see what happens when you apply the Composable DML feature here
CREATE PROC InsertParentChild
@Value1 varchar(100),
@Value2 varchar(100),
@Value3 varchar(100)
AS
INSERT INTO Child
(Parent_ID,Value2,Value3)
SELECT
FROM ID,
@Value2,
@Value3
(
INSERT INTO Parent (Value1)
OUTPUT INSERTED.ID
VALUES(@Value1)
)t
GO
Compiling the above proc gives you this error
Msg 356, Level 16, State 1, Line 5
The target table ‘dbo.Child’ of the OUTPUT INTO clause 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. Found reference constraint ‘FK_…’
Thus you can find that Composable DML cant be applied on table if it forms part of foreign key constraint.
