Sqlexception конфликт инструкции insert с ограничением foreign key

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint «FK_Sup_Item_Sup_Item_Cat». The conflict occurred in database «dev_bo», table «dbo.Sup_Item_Cat». The statement has been terminated.

insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id, 
                      status_code, last_modified_user_id, last_modified_timestamp, client_id)   
values (10162425, 10, 'jaiso', '123123',
        'a', '12', '2010-12-12', '1062425')

The last column client_id is causing the error. I tried to put the value which already exists in the dbo.Sup_Item_Cat into the column, corresponding to the sup_item.

The problem is not with client_id from what I can see. It looks more like the problem is with the 4th column, sup_item_cat_id

I would run

sp_helpconstraint sup_item

and pay attention to the constraint_keys column returned for the foreign key FK_Sup_Item_Sup_Item_Cat to confirm which column is the actual problem, but I am pretty sure it is not the one you are trying to fix. Besides ‘123123’ looks suspect as well.

answered Jun 3, 2010 at 12:44

My insert value fields contained tabs and spaces that were not obvious to the naked eye. I had created my value list in Excel, copied, and pasted it to SQL, and run queries to find non-matches on my FK fields.

The match queries did not detect there were tabs and spaces in my FK field, but the INSERT did recognize them and it continued to generate the error.

I tested again by copying the content of the FK field in one record and pasting it into the insert query. When that record also failed, I looked closer at the data and finally detected the tabs/spaces.

Once I cleaned removed tabs/spaces, my issue was resolved.

answered Jan 6, 2014 at 19:27

1. run sp_helpconstraint
2. pay attention to the constraint_keys column returned for the foreign key

In my case, I was inserting the values into the child table in the wrong order:

For the table with 2 columns: column1 and column2, I got this error when I mistakenly entered:

INSERT INTO Table VALUES('column2_value', 'column1_value');

The error was resolved when I used the below format:-

INSERT INTO Table (column2, column1) VALUES('column2_value', 'column1_value');

The problem was reproducible and intermittent for me using mybatis.
I had correct DB configuration (PK, FK, auto increment etc)
I had correct order of insertions (parent records first); in debug I could see the parent record inserted with respective PK and just after that next statement failed with inserting the child record with the correct FK inside.

The problem was fixed by reseeding identity with

DBCC CHECKIDENT ('schema.customer', RESEED, 0);
DBCC CHECKIDENT ('schema.account', RESEED, 0);

The code that failed before started to work.

I experienced same issue while running the query:

INSERT INTO [dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Meanwhile, I confirmed that the values 70004 and 150044 exist in the corresponding tables.

I fixed this by including the database name as follows:

INSERT INTO [TIS].[dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint «FK_Sup_Item_Sup_Item_Cat». The conflict occurred in database «dev_bo», table «dbo.Sup_Item_Cat». The statement has been terminated.

insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id, 
                      status_code, last_modified_user_id, last_modified_timestamp, client_id)   
values (10162425, 10, 'jaiso', '123123',
        'a', '12', '2010-12-12', '1062425')

The last column client_id is causing the error. I tried to put the value which already exists in the dbo.Sup_Item_Cat into the column, corresponding to the sup_item.

The problem is not with client_id from what I can see. It looks more like the problem is with the 4th column, sup_item_cat_id

I would run

sp_helpconstraint sup_item

and pay attention to the constraint_keys column returned for the foreign key FK_Sup_Item_Sup_Item_Cat to confirm which column is the actual problem, but I am pretty sure it is not the one you are trying to fix. Besides ‘123123’ looks suspect as well.

answered Jun 3, 2010 at 12:44

My insert value fields contained tabs and spaces that were not obvious to the naked eye. I had created my value list in Excel, copied, and pasted it to SQL, and run queries to find non-matches on my FK fields.

The match queries did not detect there were tabs and spaces in my FK field, but the INSERT did recognize them and it continued to generate the error.

I tested again by copying the content of the FK field in one record and pasting it into the insert query. When that record also failed, I looked closer at the data and finally detected the tabs/spaces.

Once I cleaned removed tabs/spaces, my issue was resolved.

answered Jan 6, 2014 at 19:27

1. run sp_helpconstraint
2. pay attention to the constraint_keys column returned for the foreign key

In my case, I was inserting the values into the child table in the wrong order:

For the table with 2 columns: column1 and column2, I got this error when I mistakenly entered:

INSERT INTO Table VALUES('column2_value', 'column1_value');

The error was resolved when I used the below format:-

INSERT INTO Table (column2, column1) VALUES('column2_value', 'column1_value');

The problem was reproducible and intermittent for me using mybatis.
I had correct DB configuration (PK, FK, auto increment etc)
I had correct order of insertions (parent records first); in debug I could see the parent record inserted with respective PK and just after that next statement failed with inserting the child record with the correct FK inside.

The problem was fixed by reseeding identity with

DBCC CHECKIDENT ('schema.customer', RESEED, 0);
DBCC CHECKIDENT ('schema.account', RESEED, 0);

The code that failed before started to work.

I experienced same issue while running the query:

INSERT INTO [dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Meanwhile, I confirmed that the values 70004 and 150044 exist in the corresponding tables.

I fixed this by including the database name as follows:

INSERT INTO [TIS].[dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Error: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint «FK__Item__order__3AE27131». The conflict occurred in database «pmall», table «dbo.ItemSaved», column ‘id’.

Here’s my table:

ItemSavedUnits

• id
• ItemID (is set in this table a FK to Item.id)
• …etc.

Here’s my insert statement:

insert into ItemSavedUnits (ItemID, name, Price)
select ItemID, name,Price
from ItemUnits where ItemID = 92439 

I don’t really understand why if I a FK constraint on ItemSavedUnits.ItemID that is related to Item.ItemID and ItemUnits has no constraints at all why I’m getting a problem inserting into ItemSavedUnits. The ItemID I’m tryign to insert does exist in the Item table.

It looks like you need a row in ItemUnits with that ID first — what does the SELECT statement part of your insert return? No rows?

Also, is there a trigger on the ItemSavedUnits table that could be causing problems?

answered Dec 1, 2009 at 19:43

answered Dec 1, 2009 at 19:45