UPDATE on Self referenced Table
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.
Re: UPDATE on Self referenced Table
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.