title: Why would an insert conflict with FK constraint when data being inserted fits?
body: <p>I'm inserting data into the Receipts table in my database. This table has a structured relationship to Stores linked on <code>Stores.Id = Receipts.StoreId</code></p>
  
  <p>Occasionally, the insert meets with the following error:</p>
  
  <blockquote>
    <p>System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Receipts_dbo.Stores_StoreId".</p>
  </blockquote>
  
  <p>I did 5000 posts to the database and this error only occurred 10 times in that batch. The data for all 5000 posts was identical (see code below).</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I've run a second batch of 5000 posts and this time all of them went through without error effectively making the chance of an error occurring 1000:1</p>
  
  <p>I've verified that there is a store in the Stores table with the Id value specified in the ajax post data.</p>
  
  <p>Can anyone tell me why only 1/500th of the posts I'm sending experiences this error?</p>
  
  <pre><code>// function gets the receipt number from the loop that calls it
  function CreateData(receiptNumber) {
      var StoreId = 5;
      var ReceiptValue = 100.50;
      var Cashback = 10.50;
  
      var Receipt = {
          "StoreId": StoreId,
          "ReceiptNumber": receiptNumber,
          "ReceiptValue": ReceiptValue,
          "Cashback": Cashback };
  
      return Receipt;
  }
  
  // function initiates the loop for x times
  function LoopPostData ( iterations ) {
  
      for ( var i = 1; i &lt;= iterations; i++ ) {
          PostData( i );
      }
  }
  
  function PostData ( iteration ) {
      var PostCompleted = false,
          ReceiptNumber = "TST-"+ pad( iteration, 4 );
  
      UpdatePostsTable( "- Attempting to post "+ ReceiptNumber );
  
      $.ajax( {
          type: "post",
          url: "http://localhost:54354/api/Receipt",
          data: CreateData( ReceiptNumber ),
          success: function () {
              var msg = "-"+ ReceiptNumber +" was saved on the API";
  
              UpdateSuccessTable( msg ); // indicate the post was saved
          },
          statusCode: {
              400: function () {
                  var msg = "-"+ ReceiptNumber +" was sent to API but not saved";
  
                  UpdateErrorsTable( msg ); // indicate the post couldn't be saved
  
                  PostCompleted = true;
              }
          },
          error: function () {
              if ( PostCompleted ) return;
  
              var msg = "-"+ ReceiptNumber +" was not sent to the API";
  
              UpdateErrorsTable( msg ); // indicate the post was sent to the API
          }
      } );
  }
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>Here's some more info.</p>
  
  <p>Here's the code that actually does the insert into the database. It uses Entity Framework</p>
  
  <pre><code>private void PostReceipt(CreateReceiptViewModel Source)
  {
      PocketPlootoEntities db = new PocketPlootoEntities();
  
      Receipt Receipt = new Receipt
      {
          Calculated = false,
          Cashback = Source.Cashback,
          Deleted = false,
          ReceiptDate = DateTime.Now,
          ReceiptNumber = Source.ReceiptNumber,
          ReceiptValue = Source.ReceiptValue,
          Redeemed = false,
          Store = db.Stores.FirstOrDefault(x =&gt; x.Id == Source.StoreId),
          StoreId = Source.StoreId,
          ValidUntil = DateTime.Now.AddDays(7)
      };
  
      db.Receipts.Add(Receipt);
      db.SaveChanges();
  }
  </code></pre>
  
  <p>This is hit by the ajax post above.</p>
  
  <p>Here's the Stores table (bear in mind that the ajax is posting to the store with Id 5 (Roman's Pizza)</p>
  
  <p><a href="https://i.stack.imgur.com/rf6Sa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rf6Sa.png" alt="Here&#39;s the Stores table"></a></p>
  
  <p>Based on this, I can't believe that the error is occurring because there is no linked record in the Stores table for the new Receipt.</p>
  
  <p><strong>EDIT 2</strong>  </p>
  
  <p>I pasted the POCO classes that Entity Framework Code-First used to create the tables as well as the SQL code to create them as well.</p>
  
  <p><a href="http://pastebin.com/7JDnbU2A" rel="nofollow noreferrer">http://pastebin.com/7JDnbU2A</a></p>
  
  <p>To save some time, here's the Foreign Key Constraint in question per the error message:</p>
  
  <pre><code>ALTER TABLE [dbo].[Receipts]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Receipts_dbo.Stores_StoreId] FOREIGN KEY([StoreId])
  REFERENCES [dbo].[Stores] ([Id])
  ON DELETE CASCADE
  GO
  </code></pre>
  
  <p><strong>EDIT 3</strong>  </p>
  
  <p>I've updated my server code only to make EF log its queries to the Output Windows in Visual Studio.</p>
  
  <p>Here's one of the INSERT commands it executed (this command was selected randomly)</p>
  
  <pre><code>INSERT [dbo].[Receipts]([StoreId], [UserId], [ReceiptNumber], 
         [ReceiptValue], [ReceiptDate], [Cashback], [ValidUntil], 
         [Calculated], [Redeemed], [Deleted], [Invoice_Id])
  VALUES (@0, NULL, @1, @2, @3, @4, @5, @6, @7, @8, NULL)
  SELECT [Id]
  FROM [dbo].[Receipts]
  WHERE @@ROWCOUNT &gt; 0 AND [Id] = scope_identity()
  
  
  -- @0: '1' (Type = Int32)
  -- @1: 'TST-0006' (Type = String, Size = -1)
  -- @2: '100.5' (Type = Decimal, Precision = 18, Scale = 2)
  -- @3: '2016/07/28 6:38:38 PM' (Type = DateTime2)
  -- @4: '10.5' (Type = Decimal, Precision = 18, Scale = 2)
  Opened connection at 2016/07/28 6:38:38 PM +02:00
  -- @5: '2016/08/04 6:38:38 PM' (Type = DateTime2)
  Started transaction at 2016/07/28 6:38:38 PM +02:00
  -- @6: 'False' (Type = Boolean)
  -- @7: 'False' (Type = Boolean)
  -- @8: 'False' (Type = Boolean)
  </code></pre>