Sql – INSERT statement conflicted with the FOREIGN KEY constraint even after deleting the records from the child table

relational-databasesqlsql-server-2008

I tired to delete the occurrences of the values which the insert stmt was trying to insert
in the dependent table. 

delete from BSC_UR_RE_AN 
where AN_ID in (1084,1083,1088,1087,1121,1122,1123,1094,5010,
                1239,1242,70187,7001,7002,1284,1285)

But no luck, I got the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint
"BSC_U_RA_AN_FK". The conflict occurred in database "DMDB", table
"dbo.BSC_AN", column 'AN_ID'.

Best Answer

That error means that ANAG_ID value you're trying to insert as FOREIGN KEY doesn't exists on the related table. So, you should not delete it, you should insert it if it's missing.

Edit: You should have a clear view of your tables design, examining the constraint of the tables you're trying to insert into.

As example, a Foreign Key constraint usually refers to a Primary Key of another table.

The error you see is because you're trying to inserd an invalid value (eg. not exists in the master table).

An example:

CREATE TABLE MasterTable (
   ID int NOT NULL PRIMARY KEY,
   Name varchar(50)
)

CREATE TABLE MasterTableValues (
   IDMasterTable INT REFERENCES MasterTable(ID), --FOREIGN KEY 
   Value varchar(50)
)

Let's assume you have this data in MasterTable:

1  FirstDate
2  SecondData
3  ThirdData
5  FifthData

Now, you can insert these values in MasterTableValues:

1  SomeValue
1  SomeOtherValue
3  SomeValue

But CANNOT insert this value:

4  SomeValue

because the key ID = 4 don't exist in MasterTable

Related Topic