Finding Circular Foreign Key References

[This article was contributed by the SQL Azure team.]

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:

1. An insert into the table referenced by the nullable foreign key with the key set to null.
2. An insert into the table with the non-null foreign key.
3. 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.

If you have circular references the output will look like this:

dbo.City -> dbo.Author -> dbo.City  dbo.Division -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image -> dbo.Division  dbo.State -> dbo.Image -> dbo.Area -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.State  dbo.County -> dbo.Region -> dbo.Author -> dbo.City -> dbo.County  dbo.Image -> dbo.Area -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image  dbo.Location -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image -> dbo.Location  dbo.LGroup -> dbo.LGroup  dbo.Region -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region  dbo.Author -> dbo.City -> dbo.Author  dbo.Area -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image -> dbo.Area

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.

SET NOCOUNT ON    -- WWB: Create a Temp Table Of All Relationship To Improve Overall Performance  CREATE TABLE #TableRelationships (FK_Schema nvarchar(max), FK_Table nvarchar(max),      PK_Schema nvarchar(max), PK_Table nvarchar(max))    -- WWB: Create a List Of All Tables To Check  CREATE TABLE #TableList ([Schema] nvarchar(max), [Table] nvarchar(max))    -- WWB: Fill the Table List  INSERT INTO #TableList ([Table], [Schema])  SELECT TABLE_NAME, TABLE_SCHEMA  FROM INFORMATION_SCHEMA.TABLES   WHERE Table_Type = 'BASE TABLE'    -- WWB: Fill the RelationShip Temp Table  INSERT INTO #TableRelationships(FK_Schema, FK_Table, PK_Schema, PK_Table)  SELECT      FK.TABLE_SCHEMA,      FK.TABLE_NAME,      PK.TABLE_SCHEMA,      PK.TABLE_NAME  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON           C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON           C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON           C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME        INNER JOIN (              SELECT i1.TABLE_NAME, i2.COLUMN_NAME              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1              INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON               i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME              WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'  ) PT ON PT.TABLE_NAME = PK.TABLE_NAME    CREATE TABLE #Stack([Schema] nvarchar(max), [Table] nvarchar(max))    GO    -- WWB: Drop SqlAzureRecursiveFind  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =       OBJECT_ID(N'[dbo].[SqlAzureRecursiveFind]') AND type in (N'P', N'PC'))  DROP PROCEDURE [dbo].[SqlAzureRecursiveFind]    GO    -- WWB: Create a Stored Procedure that Recursively Calls Itself  CREATE PROC SqlAzureRecursiveFind      @BaseSchmea nvarchar(max),      @BaseTable nvarchar(max),      @Schmea nvarchar(max),      @Table nvarchar(max),      @Fail nvarchar(max) OUTPUT  AS        SET NOCOUNT ON                      -- WWB: Keep Track Of the Schema and Tables We Have Checked      -- Prevents Looping                INSERT INTO #Stack([Schema],[Table]) VALUES (@Schmea, @Table)            DECLARE @RelatedSchema nvarchar(max)      DECLARE @RelatedTable nvarchar(max)            -- WWB: Select all tables that the input table is dependent on      DECLARE table_cursor CURSOR LOCAL  FOR            SELECT PK_Schema, PK_Table            FROM #TableRelationships            WHERE FK_Schema = @Schmea AND FK_Table = @Table        OPEN table_cursor;        -- Perform the first fetch.      FETCH NEXT FROM table_cursor INTO @RelatedSchema, @RelatedTable;        -- Check @@FETCH_STATUS to see if there are any more rows to fetch.      WHILE @@FETCH_STATUS = 0      BEGIN                -- WWB: If We have Recurred To Where We Start This          -- Is a Circular Reference          -- Begin failing out of the recursions          IF (@BaseSchmea = @RelatedSchema AND                   @BaseTable = @RelatedTable)              BEGIN                  SET @Fail = @RelatedSchema + '.' + @RelatedTable                  RETURN              END          ELSE                      BEGIN                        DECLARE @Count int                        -- WWB: Check to make sure that the dependencies are not in the stack              -- If they are we don't need to go down this branch              SELECT    @Count = COUNT(1)              FROM    #Stack                  WHERE    #Stack.[Schema] = @RelatedSchema AND                   #Stack.[Table] = @RelatedTable                        IF (@Count=0)               BEGIN                  -- WWB: Recurse                  EXECUTE SqlAzureRecursiveFind @BaseSchmea,                       @BaseTable,                       @RelatedSchema, @RelatedTable, @Fail OUTPUT                  IF (LEN(@Fail) > 0)                  BEGIN                      -- WWB: If the Call Fails, Build the Output Up                      SET @Fail = @RelatedSchema + '.' + @RelatedTable                           + ' -> ' + @Fail                      RETURN                  END              END         END                      -- This is executed as long as the previous fetch succeeds.      FETCH NEXT FROM table_cursor INTO @RelatedSchema, @RelatedTable;      END        CLOSE table_cursor;      DEALLOCATE table_cursor;        GO        SET NOCOUNT ON    DECLARE @Schema nvarchar(max)  DECLARE @Table nvarchar(max)  DECLARE @Fail nvarchar(max)    -- WWB: Loop Through All the Tables In the Database Checking Each One  DECLARE list_cursor CURSOR FOR        SELECT [Schema], [Table]        FROM #TableList    OPEN list_cursor;    -- Perform the first fetch.  FETCH NEXT FROM list_cursor INTO @Schema, @Table;    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  WHILE @@FETCH_STATUS = 0  BEGIN        -- WWB: Clear the Stack (Don't you love Global Variables)      DELETE #Stack            -- WWB: Initialize the Input      SET @Fail = ''        -- WWB: Check the Table      EXECUTE SqlAzureRecursiveFind @Schema,           @Table, @Schema,           @Table, @Fail OUTPUT      IF (LEN(@Fail) > 0)      BEGIN          -- WWB: Failed, Output          SET @Fail = @Schema + '.' + @Table + ' -> ' + @Fail          PRINT @Fail      END                   -- This is executed as long as the previous fetch succeeds.      FETCH NEXT FROM list_cursor INTO @Schema, @Table;  END    -- WWB: Clean Up  CLOSE list_cursor;  DEALLOCATE list_cursor;                            DROP TABLE #TableRelationships  DROP TABLE #Stack  DROP TABLE #TableList  DROP PROC SqlAzureRecursiveFind

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.