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.