title: MERGE statement conflicts with foreign key constraint
body: <p>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.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>The MERGE statement pulls data from a similar table in another database and populates my STCH table in the assignments database:</p>
  
  <pre><code>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 &gt; 1 AND DST17000FUHSD.DBO.TCH.ID &gt; 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;
  </code></pre>
  
  <p>This is my error message</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>When I run the subquery by itself I get correct data. TN,TE,ID,U1</p>
  
  <p>Here is the Foreign Key</p>
  
  <pre><code>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
  </code></pre>