THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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
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.*/
INSERT	[TableWithFK]([col1])
SELECT	[col1]
FROM	(
		INSERT	[TableWithPK]([col1])
		OUTPUT	[INSERTED].[col1]
		VALUES(1)
		)insert_out
;

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.

@Jamiet

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.

Published Friday, August 10, 2012 11:48 AM by jamiet

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

 

jbooker said:

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(?)

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Inserting Data Returned From an OUTPUT Clause Into a Table:

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.

Josh

August 10, 2012 8:26 AM
 

Vyacheslav said:

but it is working

DROP TABLE #TableWithPK

DROP TABLE #TableWithFK

CREATE TABLE #TableWithPK ([col1] INT PRIMARY KEY)

CREATE TABLE #TableWithFK ([col1] INT REFERENCES #TableWithPK([col1]))

INSERT #TableWithFK([col1])

SELECT [col1]

FROM (

INSERT #TableWithPK([col1])

OUTPUT [INSERTED].[col1]

VALUES(1)

)insert_out

SELECT * FROM #TableWithPK

SELECT * FROM #TableWithFK

GO

August 10, 2012 8:45 AM
 

Vyacheslav Shcherbak said:

but it is working

DROP TABLE #TableWithPK

DROP TABLE #TableWithFK

CREATE TABLE #TableWithPK ([col1] INT PRIMARY KEY)

CREATE TABLE #TableWithFK ([col1] INT REFERENCES #TableWithPK([col1]))

INSERT #TableWithFK([col1])

SELECT [col1]

FROM (

INSERT #TableWithPK([col1])

OUTPUT [INSERTED].[col1]

VALUES(1)

)insert_out

SELECT * FROM #TableWithPK

SELECT * FROM #TableWithFK

GO

August 10, 2012 8:46 AM
 

jamiet said:

Hello Vyacheslav,

Yes, it works on temporary tables because "FOREIGN KEY constraints are not enforced on local or global temporary tables."

JT

August 10, 2012 8:56 AM
 

jbooker said:

You get the same error with this statement:

INSERT [TableWithPK]([col1])

OUTPUT [INSERTED].[col1]

INTO [TableWithFK]

VALUES(1)

I usually output into a table variable or temp table then work with it like so:

BEGIN TRAN

DECLARE @TableWithFK TABLE([col1] INT);

INSERT [TableWithPK]([col1])

OUTPUT [INSERTED].[col1]

INTO  @TableWithFK

VALUES(1)

INSERT INTO [TableWithFK]

SELECT * FROM @TableWithFK

COMMIT;

Josh

August 10, 2012 11:01 AM
 

Gil said:

You can temporarily disable the constraint.

If you created the table with a named constraint (or know the name):

CREATE TABLE [TableWithFK] ([col1] INT, CONSTRAINT FK_MyFK FOREIGN KEY ([col1])  REFERENCES [TableWithPK]([col1]));

ALTER TABLE [TableWithFK] NOCHECK CONSTRAINT FK_MyFK

GO

INSERT [TableWithFK]([col1])

SELECT [col1]

FROM (

INSERT [TableWithPK]([col1])

OUTPUT [INSERTED].[col1]

VALUES(1)

)insert_out

;

GO

ALTER TABLE [TableWithFK] NOCHECK CONSTRAINT FK_MyFK

August 13, 2012 3:14 AM
 

Tom Hodson said:

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.

see here for more info:

https://connect.microsoft.com/SQLServer/feedback/details/435031

http://schottsql.blogspot.com/2011/01/sql-2008-merge-and-pkfk-constraints.html

August 13, 2012 5:15 AM
 

Gil said:

I meant that last ALTER TABLE to say CHECK rather than NOCHECK.

August 13, 2012 10:06 AM
 

Peter Henell said:

I once solved it by using dynamic SQL. It works but I can't recommend it due to readability is severely poor.

https://sites.google.com/site/mrmbookmarks/msg/sql-inserttoachildtableusingvaluesfromtheoutputclause

August 24, 2012 1:50 PM
 

Saul said:

Gil's solution worked fined! however, don't know why but in SSIS did not work the first time.

November 3, 2014 5:54 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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