title: Need Help Creating Primary Key/Foreign Key Relationships between Multiple Tables
body: <h2><strong>Background:</strong></h2>
  
  <p>(I'm using Microsoft SQL Server 2014)</p>
  
  <p>My company receives data files (<code>tblFile</code>) that contain many accounts (<code>tblAccount</code>).  For each data file, we may perform multiple "pricings" (<code>tblPricing</code>), and these "pricings" may contain all of the accounts in the file, or only a subset of them, but the "pricings" cannot contain any accounts not in the file from which the pricing is based.  So, in summary:</p>
  
  <ol>
  <li>We get a single file</li>
  <li>This file can contain many accounts</li>
  <li>We create many pricings from this single file</li>
  <li>Each pricing can contain all or some of the accounts from the file it is linked to, but no accounts not in that file</li>
  </ol>
  
  <p>Here is a (way) simplified database diagram as it exists today:
  <a href="https://i.stack.imgur.com/FrSbd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FrSbd.png" alt="Simplified Database Diagram"></a></p>
  
  <h2><strong>Problem:</strong></h2>
  
  <p>What's working so far:</p>
  
  <ul>
  <li>The 1:Many relationship between <code>tblFile</code> and <code>tblPricing</code></li>
  <li>The Many:Many relationship between <code>tblFile</code> and <code>tblAccount</code> (an account can exist in multiple files)</li>
  <li>The Many:Many relationship between <code>tblPricing</code> and <code>tblAccount</code> (because many pricings can be performed, an account can exist in many pricings)</li>
  </ul>
  
  <p>Our problem comes from trying to enforce integrity between the subset of accounts that a file has and the subset of accounts that a pricing has.  With the above structure, the <code>tblPricingAccounts</code> can contain accounts not contained in the <code>tblFileAccounts</code>, violating our need for each pricing to contain only the accounts from the file of which it is based upon.</p>
  
  <p>I've tried changing the foreign key relationships where I broke the link between <code>tblPricingAccounts</code> and <code>tblAccount</code>, removed 'acct_id' from <code>tblPricingAccounts</code>, and instead linked <code>tblPricingAccounts</code> to <code>tblFileAccounts</code> (yes, I know I need a primary key in <code>tblFileAccounts</code> and I had one).  But, then I was able to insert whatever 'pricing_id' I wanted into <code>tblPricingAccounts</code>.  Now I could link accounts to a pricing that had nothing to do with the file that originally contained those accounts.</p>
  
  <h2><strong>Need</strong></h2>
  
  <p>At the end of the day, I don't care what the structure or relationships of my database look like.  I simply need the following criteria met, and I can't seem to wrap my mind around it:</p>
  
  <ol>
  <li>A file contains many accounts.</li>
  <li>A file contains many pricings.</li>
  <li>A pricing contains many accounts, but those accounts MUST be contained in the file that the pricing is linked to.</li>
  </ol>
  
  <p>Any help is appreciated, and I'm open to all suggestions that can be performed within SQL Server.  Ultimately I'm building a web application around this database, and I'm using Entity Framework 6 to make life easier (mostly...).  I could obviously enforce the above 3 needs through my code, but I really would like the database to be the last line of defense in enforcing this integrity.</p>