SQL Server : updating foreign key referential integrity
<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>