MERGE statement conflicts with foreign key constraint
That means that the data you're deleting (by your WHEN NOT MATCHED BY SOURCE THEN DELETE;
) is referenced on your other table.
Remove that part of the query if you don't want to delete data that is not in your Source
subquery.
Minott Opdyke
Updated on March 04, 2020Comments
-
Minott Opdyke about 4 years
I have two tables. STCH is one to many to SSBT. TN in STCH is a foreign key in SSBT. SSBT is a list of assignments by TN. I want to keep STCH (employee list) updated with changes and am using a MERGE Statement.
STCH = TN(ID#), TE(name),SID(StaffID),HSE(Department),DTS(datetime stamp=today) SSBT = DATE(of assignment),TN(foreign key),PID(assignment #),SITE(bldg),DTS(datetime-today)
The MERGE statement pulls data from a similar table in another database and populates my STCH table in the assignments database:
MERGE STCH T USING (SELECT TN,TE,TCH.ID,U1 FROM DST17000FUHSD.DBO.TCH JOIN DST17000FUHSD.DBO.STJ j on j.id = DST17000FUHSD.DBO.TCH.id and (j.scl = 1 or j.scl = 0) WHERE tch.SC = 1 AND TN > 1 AND DST17000FUHSD.DBO.TCH.ID > 1 and tg = '' and j.jc = 12 and DST17000FUHSD.DBO.TCH.del = 0 ) S ON (S.TN = T.TN and s.id = t.sid) WHEN MATCHED THEN UPDATE SET T.TE = S.TE, T.SID = S.ID, T.HSE = S.U1 WHEN NOT MATCHED BY TARGET THEN INSERT (TN,TE,SID,HSE) VALUES (S.TN,S.TE,S.ID,S.U1) WHEN NOT MATCHED BY SOURCE THEN DELETE;
This is my error message
Msg 547, Level 16, State 0, Line 1 The MERGE statement conflicted with the REFERENCE constraint "FK_87". The conflict occurred in database "SUBMAN", table "dbo.SSBT", column 'TN'. The statement has been terminated.
When I run the subquery by itself I get correct data. TN,TE,ID,U1
Here is the Foreign Key
USE [SUBMAN] GO ALTER TABLE [dbo].[SSBT] WITH CHECK ADD CONSTRAINT [FK_87] FOREIGN KEY([TN]) REFERENCES [dbo].[STCH] ([TN]) GO ALTER TABLE [dbo].[SSBT] CHECK CONSTRAINT [FK_87] GO
-
Minott Opdyke about 6 yearsThat was it. Although when I ran it without the 'Delete' part I received the error ' Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.' I fixed this by grouping and it ran no error