Skip to content

Home

Jump to bottom
Stefan Kuhn edited this page Sep 10, 2017 · 36 revisions

Find circular references by SQL

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

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

Quick steps

For MS SQL server:

  1. Run the script SQL-Find-Circular-References/src/main/sql/findCircularReferences.sql
  2. You should get a list of circular references like this: source | target | path --- | --- | --- dbo.Project | dbo.ProjectAssignment | dbo.Project>dbo.ProjectTeam>dbo.ProjectAssignment dbo.Project | dbo.ProjectAssignment | dbo.Project>dbo.Task>dbo.ProjectAssignment

Other RDBMS

  1. Modify part 1 in the script. Write a query which lists all PK-FK relations in your DB.
  2. Change the few SQL Server specific things like datatypes
  3. Run your script
  4. Send me a pull-request!!! :-)

Circular references

Types of Circular References

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: Example Detail Household

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 PK-FK relations between those 2 tables

There is more then one path from the first to the second table.

Code explained

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

Code steps

  1. List all PK-FK relations
  2. Find PKs that are referenced more then once (reduces workload for next step)
  3. Find all possible paths from those PK tables to any other table (using recursive CTE)
  4. Identify problematic circles
  5. Display result nicely as paths

Part 1: List all PK-FK relations

For SQL Server: test test

Clone this wiki locally