Finding Circular Foreign Key References

In the world of relational databases circular references are schema structures where foreign keys relating the tables create a loop. Circular references cause special types of issues when trying to synchronize two relational database where the foreign keys are enforced. Because of this issue, database schemas that contain circular references are restricted in the tools that can be used when synchronizing and replicating the database. This article will explain circular references and demonstrate a Transact-SQL script for determining if your database has a circular reference.

What is a Circular Reference?

Foreign keys create database-enforced integrity constraints. These constraints ensure that a row of data exists in one table before another table can reference it. They also prevent a dependent row from being deleted that another row references. In Figure 1 we see a simple foreign key between Address table and StateProvince table in the Adventure Works database.

Figure 1

A circular reference is one or more tables where the foreign keys create a loop. Figure 2 is an example.

Figure 2

In this case the City table contains a reference to the author; it is the author that wrote the description for the city. The Author table has a reference to the city, because each author lives in a city. So which came first, the city or the author? In all cases with circular references one of the foreign key columns must be accept a null value. This allows the data to be inserted in 3 passes:

An insert into the table referenced by the nullable foreign key with the key set to null.

An insert into the table with the non-null foreign key.

An update to modify the nullable foreign key to reference the row inserted in step 2.

A circular reference is not limited to two tables, it might involve many tables, all bound together in one big circle.

Self-Referencing Tables

A special case circular reference is the self-referencing table. This is a table that has a foreign key column that references its own primary key. An example is a human resource schema that tracks employees and their bosses. In the employee table, there is a foreign key column called boss that references the primary key column in the employee table. Self-referencing tables always have a foreign key column which is nullable and at least one null exists. In the example above it would be the CEO, since he doesn’t have a boss his boss column is null.

Synchronizing Schemas with Circular References

Tables that are not involved in a circular reference are easy to synchronize, you make a complete table update the table without dependencies on it, then update the tables with foreign key dependences. In Figure 1 you would update the StateProvince table, then the Address table. This explanation is simplified, for example the deletes are done in the reverse order. If the tables have no circular references you can synchronize them table by table if you know their dependency order.

Synchronizing tables with circular references is much harder, because you have to update the tables row by row, jumping back and forth between the tables, inserting the nullable foreign key with nulls first, then updating them later. Again this is a simplified explanation; the point is that you can’t update the tables in a serial order if there are circular references.

There are really only a couple ways to synchronize database that contains tables with circular references:

Perform a transaction based replication, much like SQL Server replication, which updates, inserts, and deletes the data in the same serial order as the data was changed in the source database

Set the database into read-only mode, bulk copy the rows over to the destination database with the same primary keys, without check constraints on. Once you have moved all the tables, the source database can be taken out of read-only mode. I blog about doing this with bcp utility here.

Deduce the possible orders of inserts, updates, and deletes row by row based on the dependencies and recreate those on the destination database. This is comparable to backwards engineering the transactions it took to update, insert and delete the data.

Detecting Circular References

The Transact-SQL script below uses a recursive cursor to detect if there are any circular references in your database schema. It can be run on your SQL Server database before you try to synchronize it with SQL Azure, or you can run it on your SQL Azure database. You can run it in the Query Window of SQL Server Management Studio; the output will be displayed as in the Message section.

Each line is a circular reference, with a link list of tables that create the circle. The Transact-SQL script to detect circular references is below, however you can also download from this page. This code will work on SQL Azure and SQL Server.