How to Fix 'INSERT statement conflicted with FOREIGN KEY constraint' Errors in SQL Server?

SQL Server Foreign Key Constraint Conflict Error Troubleshooting Guide

While inserting new data into the database, you may encounter errors similar to this:

<code>Msg 547, Level 16, State 0, Line 1
INSERT 语句与 FOREIGN KEY 约束 "FK_Sup_Item_Sup_Item_Cat" 冲突。冲突发生在数据库 "dev_bo"、表 "dbo.Sup_Item_Cat" 中。语句已终止。</code>

Understanding the error message

This error indicates that a foreign key constraint violation has occurred. Foreign keys reference columns in another table to ensure data integrity. This error is thrown when you try to insert a value that does not exist in the referenced table.

Example: Sup_Item and Sup_Item_Cat tables

The error example mentioned in the question involves the Sup_Item and Sup_Item_Cat tables. The sup_item_cat_id column in Sup_Item is a foreign key that references the primary key of Sup_Item_Cat. This error occurs if you try to insert a sup_item_cat_id value that does not exist in Sup_Item_Cat.

Solution

To resolve this error, make sure the value you are trying to insert for the foreign key column exists in the referenced table. In this example, the value of the client_id column in Sup_Item should correspond to a valid client in the dbo.Sup_Item_Cat table.

Using SQL Server Management Studio

You can use SQL Server Management Studio to identify referencing and referenced columns. Execute the following command:

<code>sp_help 'dbo.Sup_Item_Cat'</code>

This will show the structure of the Sup_Item_Cat table, including primary key and foreign key relationships. Check the column where the foreign key FK_Sup_Item_Sup_Item_Cat is located, as well as the referenced table and column.

By ensuring that your data conforms to foreign key constraints, you can avoid such errors and maintain the integrity of your database records.

The above is the detailed content of How to Fix 'INSERT statement conflicted with FOREIGN KEY constraint' Errors in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!