CODEWITHSUNDEEP
c#sqlsql-server-2008t-sql
Jack
Jack

Reputation: 15872

Transaction Foreign Key Causes: "INSERT statement conflicted with the FOREIGN KEY constraint"

How can I prevent the below error when executing a SQL Server transaction?

I'm trying to add a SupplierOrder and a VehicleRecord into a set of two database tables. I'm using the following:

Table SQL Structure:

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
)

I've written a utility method which runs a set of Sql queries as a transaction:

C# Execution of a transaction:

    SqlTransaction _Transaction;
    OpenConnection();

    _Transaction = __Connection.BeginTransaction();

    try
    {
        for (int i = 0; i < Commands.Length; i++)
        {
            Commands[i].Connection = __Connection;
            Commands[i].Transaction = _Transaction;
            Commands[i].ExecuteNonQuery();
        }
        _Transaction.Commit();
        return true;
    }
    catch (SqlException e)
    {
        _Transaction.Rollback();
    }

SQL commands to be executed by the above function:

    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);

However I get the following error:

*The INSERT statement conflicted with the FOREIGN KEY constraint "FK__VSI_Suppl_Vehic_5165187F". The conflict occurred in database "jack_test", table "dbo.VSI_VehicleRecords", column 'VehicleRecordID'.*

Upvotes: 1

Views: 4853

Answers (1)

podiluska
podiluska

Reputation: 51494

You need to get the VehicleRecordID from your first query - You currently aren't setting _VehicleRecordID to any value

To do that you need to append ;SELECT Scope_Identity() after your insert SQL and execute the command via ExecuteScalar

However, it may be easier and neater to create a stored procedure that takes all the parameters for both queries and does the work on the SQL Server

eg

create proc CreateRecordAndSupplier
(
     @Stocknumber int,
     ... (etc)
)
as
begin
declare @VR int
      INSERT INTO VSI_VehicleRecords(StockNumber,Status,Make,Model,Colour,Spefication) 
      VALUES (@StockNumber, @Status, @Make, @Model, @Colour, @Specification);

      select @VR = Scope_Identity();

      INSERT INTO VSI_SupplierOrders(VehicleRecordID) VALUES (@VR) 
end

Upvotes: 2

Related Questions