• Register
0 votes
75 views
1.9k points

Please log in or register to answer this question.

2 Answers

0 votes

Solution:

You didn’t gave any expalinations for you query so I’ll explain it using my own example.

Suppose you are trying to add SupplierOrder and VehicleRecord to a set of two database tables and you are using the following:

Table Structure SQL:

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
)

And you wrote a utility method that runs a set of Sql queries as a transaction:

C # Executing 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 executed by the specified 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);   

Presently we will discover an answer for it which is: 

You need to get the VehicleRecordID from your first solicitation - you are not at present setting _VehicleRecordID to any esteem 

For this you need to add; SELECT Scope_Identity () after the SQL embed and execute the order by means of ExecuteScalar 

Notwithstanding, it very well may be less complex and cleaner to make a put away system that takes all boundaries for the two questions and accomplishes the work on SQL Server 

For instance:

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

Hope this solved your query.

113k points
edited by
0 votes

Insert statement conflicted with the foreign key constraint

After adding a primary key data, the foreign key in the child table must correspond to the primary key field in the parent table. If the condition is not respected, you will get this error. It happens when you try to insert a record with the foreign key column and this key does not exist in the foreign table. Before inserting data, the value in the foreign key must exist in the table.  The SQL Server Management Studio does not have the field values you want.

You must check the foreign key value to match the primary key. For example, “dog cat” is different from “dogcat”. You need to make sure that the two fields values match exactly. If there are tabs and spaces that are not visible to the naked eye, it can be the source of the error.

You may use this solution:

1.       Run sp_helpconstraint

2.       Pay attention to the constraint_keys column returned for the foreign key.

It is obvious, the parent table data are missing. Check the primary key data to fix the error.

Another solution is to create a value list in Excel, copied, and pasted in SQL. Then you need to run the queries and find the non-matched in FK fields. The match queries did not detect the spaces and tabs not visible to the naked eye. The INSERT recognizes the tabs and spaces and generates the error. If you copy the content of the FK field in one record and past it to the insert query; you will detect the tabs/spaces easily.

Once you cleaned tabs/spaces, your issue will be resolved.  Hope it helps!

1.9k points

Related questions

0 votes
1 answer 17 views
17 views
Problem: Where should I seek assistance with this problem : Insert statement conflicted with the foreign key constraint?
asked Jun 22 Nahil 107k points
0 votes
1 answer 7 views
7 views
Problem: What am I supposed to do here : The insert statement conflicted with the foreign key same table constraint?
asked Jun 16 Rada Gracelynn 91.8k points
0 votes
1 answer 10 views
10 views
Problem: Tell me a way that I can solve this issue : The insert statement conflicted with the foreign key constraint the conflict occurred in database.
asked Jun 10 Irenka Kiri 85.5k points
1 vote
1 answer 48 views
48 views
Problem : I am very new to the Sql Server. Currently I want to add the new foreign key to the already existing table in this table there is existing data in a column where I want to make the change. I tried this approach on a table for a ... the error message. &ldquo;The alter table statement conflicted with the foreign key constraint&rdquo;. Can someone guide me in fixing this foreign key error?
asked Apr 1, 2020 morrisBson 3.2k points
0 votes
1 answer 8 views
8 views
Problem: The insert statement conflicted with the foreign key?
asked Jun 26 Mezbah 110k points
0 votes
1 answer 11 views
11 views
Problem: Is it possible to make this work : Delete statement conflicted with the reference constraint?
asked Jun 24 Nahil 107k points
0 votes
1 answer 9 views
9 views
Problem: Is there anyone who can help me find a solution : The update statement conflicted with the reference constraint?
asked Jun 21 Mezbah 110k points
0 votes
1 answer 10 views
10 views
Problem: Please provide me the solution if the alter table statement conflicted with the check constraint .
asked Jun 21 Diederich Raf 103k points
0 votes
1 answer 33 views
33 views
the delete statement conflicted with the reference constraint
asked Feb 16 ArifulIslam 9.9k points
0 votes
1 answer 70 views
70 views
Problem: Can anyone simplify this? >Insert or update on table violates foreign key constraint
asked May 10 Chi Omega 159k points