title: Transaction Foreign Key Causes: "INSERT statement conflicted with the FOREIGN KEY constraint"
body: <p>How can I prevent the below error when executing a SQL Server transaction?</p>
  
  <p>I'm trying to add a <code>SupplierOrder</code> and a <code>VehicleRecord</code> into a set of two database tables. I'm using the following:</p>
  
  <h3>Table SQL Structure:</h3>
  
  <pre><code>CREATE TABLE VSI_VehicleRecords
  (
      VehicleRecordID INT IDENTITY(1,1) PRIMARY KEY,
      StockNumber INT NOT NULL,
      Status INT NOT NULL,
      Make VARCHAR(50) NOT NULL,
      Model VARCHAR(50) NOT NULL,
      Colour VARCHAR(50) NOT NULL,
      Spefication VARCHAR(255) NOT NULL
  )
  
  CREATE TABLE VSI_SupplierOrders
  (
      SupplierOrderID INT IDENTITY(1,1) PRIMARY KEY,
      VehicleRecordID INT FOREIGN KEY REFERENCES VSI_VehicleRecords(VehicleRecordID) UNIQUE,
      Timestamp
  )
  </code></pre>
  
  <p>I've written a utility method which runs a set of Sql queries as a transaction:</p>
  
  <h3>C# Execution of a transaction:</h3>
  
  <pre><code>    SqlTransaction _Transaction;
      OpenConnection();
  
      _Transaction = __Connection.BeginTransaction();
  
      try
      {
          for (int i = 0; i &lt; Commands.Length; i++)
          {
              Commands[i].Connection = __Connection;
              Commands[i].Transaction = _Transaction;
              Commands[i].ExecuteNonQuery();
          }
          _Transaction.Commit();
          return true;
      }
      catch (SqlException e)
      {
          _Transaction.Rollback();
      }
  </code></pre>
  
  <h3>SQL commands to be executed by the above function:</h3>
  
  <pre><code>    SqlCommand[] _Commands = new SqlCommand[2];
  
      string _InsertVehicleQuery = "INSERT INTO VSI_VehicleRecords(StockNumber,Status,Make,Model,Colour,Spefication) VALUES (@StockNumber, @Status, @Make, @Model, @Colour, @Specification);";
  
      SqlCommand _InsertVehicleCommand = new SqlCommand(_InsertVehicleQuery);
      _InsertVehicleCommand.Parameters.AddWithValue("@StockNumber", __StockNumber);
      _InsertVehicleCommand.Parameters.AddWithValue("@Status", __Status);
      _InsertVehicleCommand.Parameters.AddWithValue("@Make", Make);
      _InsertVehicleCommand.Parameters.AddWithValue("@Model", Model);
      _InsertVehicleCommand.Parameters.AddWithValue("@Colour", Colour);
      _InsertVehicleCommand.Parameters.AddWithValue("@Specification", Specification);
  
      _Commands[0] = _InsertVehicleCommand;
  
      string _InsertSupplierOrderQuery = "INSERT INTO VSI_SupplierOrders(VehicleRecordID) VALUES (@VehicleRecordID);";
      SqlCommand _InsertSupplierOrderCommand = new SqlCommand(_InsertSupplierOrderQuery);
      _InsertSupplierOrderCommand.Parameters.AddWithValue("@VehicleRecordID", _VehicleRecordID);
  
      _Commands[1] = _InsertSupplierOrderCommand;
  
      DataUtility.NonQueryTransaction(_Commands);   
  </code></pre>
  
  <p>However I get the following error:</p>
  
  <blockquote>
    <p>*The INSERT statement conflicted with the FOREIGN KEY constraint "FK__VSI_Suppl_<em>Vehic</em>_5165187F". The conflict occurred in database
    "jack_test", table "dbo.VSI_VehicleRecords", column 'VehicleRecordID'.*</p>
  </blockquote>