Insert over DML is not allowed on a table with a foreign key constraint - by BrentMcC

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
0
Sign in
to vote
ID 535753 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 2/23/2010 3:27:32 PM
Access Restriction Public

Description

The INSERT OVER DML functionality is not allowed when the target table of the insert statement is on either side of a PK/FK relationship. The following error is produced when trying to run the INERT INTO with a MERGE statement...

Msg 356, Level 16, State 1, Procedure usp_load_customer_table, Line 161
The target table 'crm.customer_address' 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. Found reference constraint 'FK_CUSTOMER_CUSTADD_C_CUSTOMER'.

This is my code...

INSERT INTO crm.customer_address (access_no, address1, address2, address3, post_code, address_type, international_address, end_date) 

SELECT access_no, address1, address2, address3, post_code, address_type, international_address, CAST(GETDATE() AS DATE) FROM (	
MERGE INTO crm.customer_address AS tgt USING staging.stage_customer AS src
ON tgt.access_no = src.access_no and tgt.address_type = 'R' AND tgt.end_date IS NULL WHEN MATCHED AND ( tgt.address1 <> src.resaddr1 OR tgt.address2 <> src.resaddr2
OR tgt.address3 <> src.resaddr3 OR tgt.post_code <> src.respcode )
 THEN UPDATE
SET tgt.address1 = src.resaddr1,
tgt.address2 = src.resaddr2,
tgt.address3 = src.resaddr3,
tgt.post_code = src.respcode WHEN NOT MATCHED THEN INSERT ( 
access_no, address1, address2, address3, post_code, address_type )
VALUES
(
src.access_no,
src.resaddr1,
src.resaddr2,
src.resaddr3,
src.respcode,
'R'
) 
OUTPUT $ACTION, src.access_no, DELETED.address1, DELETED.address2, DELETED.address3, DELETED.post_code, DELETED.address_type, DELETED.international_address)
CHANGES (ACTION, access_no, address1, address2, address3, post_code, address_type, international_address)
WHERE ACTION = 'update';

Sign in to post a comment.
Posted by Microsoft on 3/24/2011 at 2:21 PM
Hello Brent,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen this suggestion or respond to this message and we will take another look.

Jan
SQL Server Engine
Posted by Microsoft on 2/24/2010 at 10:53 AM
Hi Brent,
Thanks for your feedback. We will consider removing the restriction in a future version of SQL Server.

--
Umachandar, SQL Programmability Team