CODEWITHSUNDEEP
sqlsql-serversql-server-2008
nitendra jain
nitendra jain

Reputation: 453

Getting error: INSERT statement conflicted with the FOREIGN KEY constraint

I am working on integration part of a portfolio product where I got preinstalled database in SQL server 2008. I can’t insert or update the database directly I can do this by in API provided by this product’s developers.

Whenever I am inserting a particular trade type through API, I am getting error message –

The database 'usp_Financial_KernelComponents_Index_Insert_Batch_5' command failed. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Fixings_id_Index_fixingsId". The conflict occurred in database "Portfolio_new", table "dbo.Financial_Shared_Fixings", column 'id'.

How could I resolve this error OR what could be the possible reason of this error. Please suggest

Upvotes: 1

Views: 15260

Answers (5)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

This error happens whern you are trying to insert a value in foreign key column, which this value does not exists in it's parent table. for example you are trying to insert value X to CustomerId in Order table, which this value does not exists in Customer table. This error occurred because we need to have a good strategy for Referential Integrity. So the only you need to do, is to check your new values(which you are going to insert them into table) to find out that is there any value compromising this rule or not.

Please check the following question where you may find the same information about this error Foreign Key constraint failure and error message when inserting values

Upvotes: 1

Arun Kumar
Arun Kumar

Reputation: 337

Before inserting the record in the child table u need to insert in the parent table (i.e) the table where the reference column is reffered to

Upvotes: -1

Bohemian
Bohemian

Reputation: 424983

In the simplest of terms, you are inserting a "child" row before inserting the parent row.

At the instant you insert the child row, the child-parent (ie foreign key) constraint is violated - the child's parent appears to not exist.

To fix, reverse the order of insertion to be parent, then child(ren).

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

A foreign key constraint means that a value of a column or a group of columns must be present in a row of another table, usually as its primary key. Here is an example:

create table country (id int not null, name varchar(100))
create table city(id int not null, country_id int, name varchar(100))

If you set up a foreign key constraint on city requiring that country_id be present in the country table, an attempt to insert a city with an invalid country_id would result in a foreign key constraint violation.

In your case, the API probably takes a set of values, one of which must be an ID of some sort (or a secondary key through which an ID can be retrieved). When you pass an invalid ID to such an API, the foreign key constraint violation is triggered.

Upvotes: 2

Reno
Reno

Reputation: 1319

You are trying to insert a record with a value in the foreign key column that doesn't exist in the foreign table.

Upvotes: 3

Related Questions