title: SQL Server : updating foreign key referential integrity
body: <p>I have the following tables in a SQL Server database: A, B &amp; C</p>
  
  <ul>
  <li>A has a primary key <code>ID</code>  </li>
  <li>B has a primary key containing <code>ID</code> and <code>A_ID</code>.  <code>A_ID</code> is also a foreign key to <code>A.ID</code>.  </li>
  <li>C has a primary key <code>ID</code> and a foreign key containing <code>A_ID</code> and <code>B_ID</code></li>
  </ul>
  
  <p>I want to update B and C's A_ID to a different record, e.g.</p>
  
  <pre><code>UPDATE B SET A_ID = 2 WHERE ID = 1
  UPDATE C SET A_ID = 2 WHERE B_ID = 1
  </code></pre>
  
  <p>Unfortunately due to referential integrity, I won't be able to update B.A_ID.  If I could, C would have an invalid foreign key.</p>
  
  <p><a href="http://sqlfiddle.com/#!6/89736/4" rel="nofollow">SQL Fiddle here</a></p>
  
  <p>I suppose one way to do this would be to delete the foreign key constraint in C and recreate it after the query has run. Is there a way to do this without altering the table structure?</p>