Break Circular dependence between table in SQL Server

By Hemanth Kumar Blogs | SQL Feb 04, 2013
In this article i will describe how to Break Circular dependence between table in SQL Server.

We have two tables ‘TABLE A' and ‘TABLE B' with Primary and Foreign Key Constraints as shown below

TABLE A                                                    TABLE B

(FK)Column P                                            Column P (PK) (FK)

(PK)Column Q                                           Column R(FK)

TABLE A has the following constraints:

Column P – Foreign Key which is referring to Column P of TABLE B

Column Q – Primary Key which is referred by Column P and Column R of TABLE B

TABLE B has the following constraints :

Column P – This acts as both Primary Key & Foreign Key

                    Primary Keyàwhen referred by Column P of TABLE A

                    Foreign Keyà when it is referring to Column Q of TABLE A

Column R –  Foreign Key which is referring to Column Q of TABLE A

Circular Dependency : Here when we try to delete a record from TABLE A, it throws an error message as Column P & Column R of TABLE B are depending on Column Q of TABLE A.  When we try to delete a record from TABLE B, it again throws an error message as Column P of TABLE A is depending on Column P of TABLE B. In this case we are prevented from deleting the data from either of the tables because of the circular dependency existing between the tables.

Solution: To delete the records from the tables when circular dependency exists between the tables, we need to break the constraints. We cannot delete the constraints straight way as the tables already contain huge data. In this scenario, update the foreign key in one of the tables to null such that it removes dependencies on one of the tables i.e. Update Column P of TABLE A to null. Once it is updated to null, Column P & Column R of TABLE B have no more dependencies and data can be deleted from TABLE B. Data deletion from TABLE B indicates that there exists no data in TABLE B which is dependent on TABLE A. Finally come back to TABLE A and delete the data which is no more required.

COMMENT USING

PREMIUM SPONSORS

Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.

Latest Blogs