Composable DML cannot insert into a table with a FOREIGN KEY

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*/USE tempdb
CREATETABLE [TableWithPK]([col1] INTPRIMARYKEY);CREATETABLE [TableWithFK]([col1] INTREFERENCES [TableWithPK]([col1]));/*Following statement fails with error:Msg 356, Level 16, State 1, Line 17The 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.*/INSERT [TableWithFK]([col1])SELECT [col1]
FROM (INSERT [TableWithPK]([col1])OUTPUT [INSERTED].[col1]
VALUES(1))insert_out
;

Comment Notification

Comments

I think this may relate to the restrictions of OUTPUT INTO clause listed on article below. It sounds like both the DML and OUTPUT INTO must succeed or both will fail, so maybe they don't support constraints or triggers in the OUTPUT INTO to prevent recursion(?)

When you are capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement and inserting those results into a target table, keep the following information in mind:

The whole operation is atomic. Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.

The following restrictions apply to the target of the outer INSERT statement:

The target cannot be a remote table, view, or common table expression.

The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.

Triggers cannot be defined on the target.

The target cannot participate in merge replication or updatable subscriptions for transactional replication.

The following restrictions apply to the nested DML statement:

The target cannot be a remote table or partitioned view.

The source itself cannot contain a <dml_table_source> clause.

The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause.

@@ROWCOUNT returns the rows inserted only by the outer INSERT statement.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.

Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.

In the <dml_table_source> clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.

I came across this when using a MERGE statement. One way to get around it was to insert into a temp table that matches the definition of the destination table and then insert the contents of that temp table into the destination.