Why Does My SQL Server INSERT Statement Conflict with a FOREIGN KEY Constraint?

Troubleshooting SQL Server's FOREIGN KEY Constraint Errors

SQL Server frequently generates the "INSERT statement conflicted with the FOREIGN KEY constraint" error. This happens when you try to insert data into a table linked to another via a foreign key relationship.

The Problem:

This error arises during an INSERT operation if the foreign key value in the target table doesn't match any existing primary key in the referenced table.

Example Error Message:

<code>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.</code>

This message indicates an attempted insertion into the Sup_Item table failed because the sup_item_cat_id foreign key value ('123123') doesn't exist as a primary key in the Sup_Item_Cat table.

The Solution:

The fix is straightforward: ensure the foreign key value you're inserting matches an existing primary key in the referenced table. In our example, a matching sup_item_cat_id must already exist in Sup_Item_Cat.

Use SQL Server Management Studio's sp_help command to examine foreign key constraints and related tables. For instance, sp_help 'dbo.Sup_Item_Cat' provides details on foreign key constraints for that table, helping you identify the referenced table and column, and confirm your INSERT statement uses valid data.

The above is the detailed content of Why Does My SQL Server INSERT Statement Conflict with a FOREIGN KEY Constraint?. For more information, please follow other related articles on the PHP Chinese website!