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.

Advertisements