MERGE statement conflicts with foreign key constraint

Question

I have two tables. STCH is one to many to SSBT. Column 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 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;

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).

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

Answer 1

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.