Foreign keys from 2 columns of the same table to the same primary key
<p>I have two tables:</p>

<p><strong>users</strong></p>

<pre><code>id  |  username
--------------
 1  |  James
 2  |  John
 3  |  Jack
 4  |  Jim
 5  |  Jane
 6  |  Jessica
</code></pre>

<p><strong>juniors</strong></p>

<pre><code>senior_id  |  junior_id
-----------------------
 1         |  4
 1         |  6
 2         |  3
 4         |  5
</code></pre>

<p>Both the <code>senior_id</code> and <code>junior_id</code> refer to the <code>users</code> table's <code>id</code> column.</p>

<p>How do I set it up so that a) it is guaranteed that both <code>senior_id</code> and <code>junior_id</code> exist in <code>users</code> and b) a <code>delete</code> from <code>users</code> will cascade and remove any instances of the deleted id from <code>juniors</code> whether it's a <code>senior_id</code> or <code>junior_id</code>? I.e. deleting Jim in the example above will automatically remove both the first and last record from juniors.</p>

<p>I tried doing this with two separate constraints. But if I set up cascade on both constraints I get a 'may cause cycles or multiple cascade paths' error. </p>

<p>And if I set it on only one constraint then a delete fails (in a case like Jim) because it cascades to only one of the columns and thus then violates the constraint.</p>

<p>I have to be able to do this on SQL Server 2005.</p>