Foreign Keys – Dependency Tracking

Every now and again, we come across the need to determine the relationships between a set of tables, and the order in which data should be inserted or deleted in them, in accordance with the rules of referential integrity. There is a dearth of information on the internet regarding this subject, so I decided to write my own script and publish it.

The script accepts a list of tables as an input parameter. It then works through the relationships between these tables and the tables they refer in the database. If the latter tables reference additional tables, then they get pulled in as well. Therefore, when the script completes, it prints the entire referential integrity ‘spider web’ of tables linked to the tables supplied in the input list.

It also prints the tables in order, i.e. the order in which data should be inserted. Therefore, the order in which data should be deleted is the opposite of the printed order.

The script’s code is shown below. The output is from the AdventureWorks database. You can download the script’s code from the attachment at the end of the article.

Multiple tables can be fed into the script. The example shown below uses only 1 table (Sales.Customer) from the AdventureWorks database.

The output of the script is shown below. It contains the list of referenced tables listed in order. The ‘lvl’ column indicates the level of depth in the referential integrity chain. The table passed in as the input (Sales.Customer) is not listed in the output. It can be assumed that it is present at level zero.

The script’s code can be downloaded from here –> Script.


About Ajit Ananthram
Ajit is a Microsoft Certified Specialist in Big Data Analytics Solutions, Microsoft Certified Solutions Associate (Business Intelligence) and a Certified Scrum Master. He has a Masters degree in IT from the University of Technology Sydney and over 10 years of industry experience working with data in roles such as Data Architect and Tech Lead. He currently works as a Senior Consultant and Microsoft Ambassador at Servian in Sydney, Australia.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: