title: Specify foreign key on one column and the value of another column
body: <p>I have a table <code>ASSETS</code> that has a structure as it is shown below :</p>
  
  <pre><code>----------------------------------------------------
  ID (PK) | DESCRIPTION | TYPE | Do- | Do+ | Dx- | Dx+
  ----------------------------------------------------
  </code></pre>
  
  <p><code>TYPE</code> column has a <em>foreign key</em>, possible values are <code>SECURITY</code> or <code>CURRENCY</code> (i.e. <em>FX</em>), also I have two more tables : <code>CURRENCIES</code> (<em>for example</em>, <code>EUR</code>, <code>RUB</code> or <code>USD</code>) :</p>
  
  <pre><code>--------------------------------------------------------
  ID (PK)| FROM (FK ASSETS.ID) | TO (FK ASSETS.ID) | VALUE
  --------------------------------------------------------
  </code></pre>
  
  <p>and <code>SECURITIES</code> (<em>for example</em>, <code>MTS</code>, <code>GAZP</code> or <code>VTB</code>) : </p>
  
  <pre><code>----------------------------------------------------------
  ID (PK)(FK ASSETS.ID)| CURRENCY (PK)(FK ASSETS.ID) | VALUE
  ----------------------------------------------------------
  </code></pre>
  
  <p>How I can make a constraint, that not only acts like foreign key in <code>CURRENCIES.FROM</code>, <code>CURRENCIES.TO</code> and <code>SECURITIES.CURRENCY</code>,but also checks if referring <code>ASSETS.TYPE</code> is <code>CURRENCY</code>, and in <code>SECURITIES</code>  also checks if referring <code>ASSETS.TYPE</code> for <code>SECURITIES.ID</code>  is <code>SECURITY</code>?</p>
  
  <p>I guess I can write triggers to check <code>ASSETS.TYPE</code> value, but I am searching for another solution right now (<em>if it is possible, of course</em>).</p>
  
  <p><em>If there are better ways to do the things a want (as a better database design), please, share your ideas.</em></p>
  
  <p><em>P.S. I guess it is quite a common problem, so if there are articles about it or similar questions asked on this network or some general-case-solutions, feel free to share.</em></p>