Home

I'm facing a database with circular references and was wondering if I could detect them by script. The presented solution is for MS SQL Server, but I think it can be adapted easily for other RDBMS as long as they support recursive CTE.

Circular references

This is a circular reference: There are 2 paths from ProjectAssignment to Project, leading to potentially different results!

• ProjectAssignment -> Task -> Project
• ProjectAssignment -> ProjectTeam -> Project

There are 3 types of circular references that come to my mind:

1. True circles, endless loop: A -> B -> C -> A
2. Self-references, parent-child
3. Multi-table circular-references

True circles, endless loop: A -> B -> C -> A

Welcome to a chicken-egg situation and good luck entering data! These will be detected soon, so let's not worry about them.

Self-references, parent-child

These are unproblematic. If parent=null marks root nodes, then just add a check-constraint to ensure that the parent-id is not the child-id itself.

Multi-table circular-references

These are the ones that we want to detect! Here's another example:

Characteristics of circular references

What identifies such circular references?

Primary key referenced more then once

One element of such circles is a table, whose primary key is referenced by more then one other table.

Foreign key to more then one table

On the other side of the circle is a table with FK references to more then one table.

Several chains of FK-PK relations between those 2 tables

These 2 tables are by more then one path

Find multi-table circular-references

So the question is: can we find such circular references by script? Let's try ..