Breaking Circular dependency between tables by heymanthkumar

VIEWS: 10 PAGES: 1

More Info
									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.

								
To top