title: Cleanup of tables before adding FK's
body: <h2>Tables</h2>
  
  <p><code>Customer</code>: <code>ID</code> (PK), various fields.</p>
  
  <p><code>CustomerDeleted</code>: Almost exact copy of the <code>Customer</code> table. Customers do frequently get duplicated in the <code>Customer</code> table (the process of adding a new customer only checks using common fields such as name and location, so if the user adding him/her mispells a name or a location, there'll be two records for the same customer in the <code>Customer</code> table), and as soon as a duplication is spotted, the last entry added will be removed from the <code>Customer</code> table and inserted into the <code>CustomerDeleted</code> table with a column storing the current/valid Customer ID.</p>
  
  <p><code>Beneficiary</code>: ID (PK), various fields.</p>
  
  <p><code>CustomerBeneficiary</code>: <code>CustomerID</code>, <code>BeneficiaryID</code> (PK both). Link table between the <code>Customer</code> and <code>Beneficiary</code> tables. Each customer can have a certain number of beneficiaries.</p>
  
  <h2>What I'd like to do</h2>
  
  <p>Set the <code>CustomerID</code> and <code>BeneficiaryID</code> in the <code>CustomerBeneficiary</code> table as FK's of their respective tables.</p>
  
  <h2>The issue</h2>
  
  <p>There may be records in <code>CustomerBeneficiary</code> with beneficiaries linked to both a current <code>Customer</code> and a <code>CustomerDeleted</code>, so if you simply try adding the FK pointing to the <code>Customer</code> table it will give you the following error</p>
  
  <blockquote>
    <p>The ALTER TABLE statement conflicted with the FOREIGN KEY constraint</p>
  </blockquote>
  
  <h2>How to...?</h2>
  
  <p>I need the <code>CustomerBeneficiary</code> table updated so that all records containing <code>CustomerDeleted</code> customer ID's are instead pointing to the valid/current customer ID. It is possible that a deleted customer ID is linked to the same beneficiary ID than the current customer ID, so an <code>UPDATE</code> statement cannot be executed as there would be duplicate PK's in the <code>CustomerBeneficiary</code> table. </p>
  
  <p>I think that there will have to be an <code>INSERT</code> (to add new rows so that the current customers are pointing out to any beneficiary their duplicate account was linked to), followed by a <code>DELETE</code> (to get rid of all the rows in the <code>CustomerBeneficiary</code> table whose <code>CustomerID</code> is part of a deleted customer).</p>
  
  <p>Now, although I know what needs to happen my SQL knowledge is pretty limited and I have absolutely no idea whatsoever of how to do such complex query. I hope that I have explained the situation well enough someone can give a hand.</p>