Thursday, January 13, 2011

SQLSERVER Foreign Key Constraint Resolution

Recently I came across a scenario in db cleanup where we need to drop and restore the foreign keys in data migration. Some times we face an issue, foreign key constraint error during both dropping and adding foreign key relationship.
Eg:
tableA(pkEmpID, Name)
tableB(pkDeptID,fkEmpID,Name) where fkEmpID is foreign key for tableA and the foreign key constraint is fkEmpID_tableA_tableB.

To cleanup the data in tableA without deleting the data in tableB, use below syntax and this step is simple.

ALTER TABLE tableA DROP Constraint fkEmpID_tableA_tableB

After this, do db cleanup and complete the data migration.
Again while restoring back the foreign key relationship, use below syntax

ALTER TABLE tableA ADD Constraint fkEmpID_tableA_tableB FOREIGN KEY (pkEmpID) REFERENCES tableB (fkEmpID)

Sometimes the above query fails due to the difference in data between tableA and tableB w.r.t forienkey i.e. fkEmpID. To avoid this issue use below approach

DELETE FROM tableB WHERE fkEmpID NOT IN (SELECT pkEmpID from tableA)
The above query deletes the unmatching EmpID data from tableB, so restoring foreign key will be possible.

Hope this approach helps SQL guys in dealing with foreign key constraint errors.
Please post if you found any other solution or having any suggestions.

Reference
DP (http://dptechnicalblog.blogspot.com/)

No comments:

Post a Comment