UPDATE on Self referenced Table

[vinitsankhe]

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.

[exterminator]

The first insert gives an error and it is right to do so. For example, take this case. You have 4 rows of data as follows:

India, Pakistan
India, Bhutan
Bhutan, India
Pakistan, India

Right? Now, for the first row - when it tries to insert it the Pakistan record does not exist in the table so this is a problem (because it is the 4th row which is not yet inserted). As you have a relationship that requires existence of Pakistan.

The right way to do it is:
1. Drop the relationship from the OZIM_LOCATION table. (at this point table is empty)
2. Do the insert with the query you posted first - INSERT INTO ... (SELECT * ...)
3. Re-create the relationship.

You should not have any problems if you follow this approach. Hope this helps.