Hi Guys,

I use MS-SQL SERVER 2005. Where I have 2 tables .... OZIM_LOCATION & TEST_LOCATION


Their Structure is ...

OZIM_LOCATION(LOC_ID varchar(50), NEIGHBOR_LOC_ID Self Foreign Key of LOC_ID)

TEST_LOCATION(LOC_ID varchar(50), NEIGHBOR_LOC_ID varchar(50))

TEST_LOCATION is actually the copy of OZIM_LOCATION taken few days back when my database was re-installed.

Now OZIM_LOCATION is Empty. I try to insert data into OZIM_LOCATION from TEST_LOCATION like this ...

INSERT INTO OZIM_LOCATION
SELECT * FROM TEST_LOCATION;

but it errors out saying ....

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__OZIM_LOCA__NEIGH__4F7CD00D". The conflict occurred in database "VINDB", table "dbo.OZIM_LOCATION", column 'LOC_ID'.

So I did this insert first ...

INSERT INTO OZIM_LOCATION(LOC_ID)
SELECT LOC_ID FROM TEST_LOCATION;

It inserted 11 records

And then I try to do .....

UPDATE OZIM_LOCATION
SET OZIM_LOCATION.NEIGHBOR_LOC_ID = TEST_LOCATION.NEIGHBOR_LOC_ID
FROM OZIM_LOCATION
INNER JOIN TEST_LOCATION
ON ( OZIM_LOCATION.LOC_ID = TEST_LOCATION.LOC_ID
AND TEST_LOCATION.NEIGHBOR_LOC_ID IS NOT NULL );

It again gives me error ....

The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__OZIM_LOCA__NEIGH__4F7CD00D". The conflict occurred in database "VINDB", table "dbo.OZIM_LOCATION", column 'LOC_ID'.


I checked that TEST_LOCATION has neighbor_loc_id that are correctly one of the LOC_IDs in the table. So why this FK constraint error?

How will I solve this?
Thx.