Find Self Referencing and Circular Referencing tables

Bob Blackburn

2015-02-12

There are times during development, ETL, or data conversion where you run into self referencing and Circular Referencing tables. A common example of a self referencing table is the Employee table who has a manager that points back to the Employee table. Sometimes a circular reference is created intentionally or unintentionally. We will not discuss architecture best practices here. We will document the references so you can continue with your work.

Create a test database with a few tables

Create Database DemoReference

go

Use DemoReference

go

CREATE TABLE Island (

    IslandID                     INT           NOT NULL,

    IslandName                   NVARCHAR (60) NOT NULL,

    CONSTRAINT [PK_Island] PRIMARY KEY CLUSTERED (IslandID ASC)

);

CREATE TABLE Employee (

    EmployeeID                          INT           NOT NULL,

    ManagerID                    INT           NULL,

    EmployeeName                 NVARCHAR (60) NOT NULL,

    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (EmployeeID ASC),

    CONSTRAINT [FK_Employee_Manager] FOREIGN KEY (ManagerID) REFERENCES Employee (EmployeeID)

);

CREATE TABLE Country (

    CountryID                    INT           NOT NULL,

    CountryName                  NVARCHAR (60) NOT NULL,

    CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED (CountryID ASC)

);

CREATE TABLE USState (

    USStateID                    INT           NOT NULL,

       CountryID                        INT           NOT NULL,

       JurisdictionID                          INT           NOT NULL,

    StateName                    NVARCHAR (60) NOT NULL,

    CONSTRAINT [PK_USState] PRIMARY KEY CLUSTERED (USStateID ASC),

       CONSTRAINT [FK_USState_Country] FOREIGN KEY (CountryID) REFERENCES Country (CountryID)

      

);

CREATE TABLE [Jurisdiction] (

    [JurisdictionID]             INT           NOT NULL,

    [ParentID]                          INT           NULL,

    [Name]                              NVARCHAR (60) NOT NULL,

       [USStateID]                      INT                        NULL,

    CONSTRAINT [PK_Jurisdiction] PRIMARY KEY CLUSTERED ([JurisdictionID] ASC),

    CONSTRAINT [FK_Jurisdiction_Parent] FOREIGN KEY ([ParentID]) REFERENCES [Jurisdiction] ([JurisdictionID]),

    CONSTRAINT [FK_Jurisdiction_USState] FOREIGN KEY ([USStateID]) REFERENCES [USState] ([USStateID])

);

CREATE TABLE USCity (

    USCityID                     INT                 NOT NULL,

    USStateID                    INT           NOT NULL,

       CountryID                        INT           NOT NULL,

    StateName                    NVARCHAR (60) NOT NULL,

    CONSTRAINT [PK_USCity] PRIMARY KEY CLUSTERED (USCityID ASC),

       CONSTRAINT [FK_USCity_USState] FOREIGN KEY (USStateID) REFERENCES USState (USStateID),

       CONSTRAINT [FK_USCity_Country] FOREIGN KEY (CountryID) REFERENCES Country (CountryID)

);

You get the following Diagram.

Now we have some code to find our self and circular referencing tables.

The Circular Reference query can take a long time to run. This is because of recursion and the use of functions in a where clause.  On a small database it took about 1 minute. Sometimes on a larger database it will run faster when they do not contain circular references. Since this is used for analysis and not as a production query, we are not as concerned with performance.

-- Self Referencing

SELECT KCU.constraint_name,

       kcu.table_schema + '.' + KCU.table_name AS TABLE_NAME,

       kcu.column_name,

       TC.table_schema + '.' + tc.table_name   AS REFERENCED_TABLE,

       kcu2.column_name                        AS REFERENCED_COLUMN

FROM   information_schema.key_column_usage kcu

       JOIN information_schema.referential_constraints rc

         ON kcu.constraint_name = rc.constraint_name

            AND KCU.constraint_schema = RC.constraint_schema

       JOIN information_schema.table_constraints tc

         ON rc.constraint_schema = tc.constraint_schema

            AND rc.unique_constraint_name = tc.constraint_name

       JOIN information_schema.key_column_usage kcu2

         ON kcu2.constraint_name = tc.constraint_name

            AND KCU2.constraint_schema = tC.constraint_schema

WHERE  kcu.table_name = tc.table_name;

--- Circular Reference

WITH circularrefcte (tableschema, tablename, refpath, reflevel)

     AS (SELECT t.table_schema,

                t.table_name,

                Cast(t.table_schema + '.' + t.table_name AS VARCHAR(max)) AS

                RefPath,

                1

         FROM   information_schema.tables t

         WHERE  t.table_type = 'BASE TABLE'

         UNION ALL

         SELECT kcu.table_schema,

                kcu.table_name,

                Cast(cr.refpath + '  ' + kcu.table_schema + '.'

                     + kcu.table_name + ' ' AS VARCHAR(max)),

                reflevel + 1

         FROM   information_schema.key_column_usage kcu

                JOIN information_schema.referential_constraints rc

                  ON kcu.constraint_name = rc.constraint_name

                     AND KCU.constraint_schema = RC.constraint_schema

                JOIN information_schema.table_constraints tc

                  ON rc.constraint_schema = tc.constraint_schema

                     AND rc.unique_constraint_name = tc.constraint_name

                JOIN circularrefcte cr

                  ON tc.table_schema = cr.tableschema

                     AND tc.table_name = cr.tablename

         WHERE  reflevel < 100                          -- Set recursive level limit

                AND kcu.table_name <> cr.tablename

                AND Charindex(kcu.table_schema + '.' + kcu.table_name,

                    cr.refpath, 2)

                    =

                    0) -- WITH CircularRefCTE

SELECT *

FROM   circularrefcte

WHERE  Charindex(tableschema + '.' + tablename + ' ', refpath, 1) > 0

       -- First Circular Reference

       AND Charindex(tableschema + '.' + tablename + ' ', refpath,

               Charindex(tableschema + '.' + tablename + ' ', refpath, Charindex

               (

               tableschema +

               '.', refpath) + 1 )

                                                                   + 1) = 0

       -- only first reference found

       AND Charindex(tableschema + '.' + tablename + ' ', refpath) =

           Charindex(tableschema

                     + '.', refpath) -- Start position

To find the Circular reference, we created a recursive CTE. You can set the recursion level in the WHERE clause. The CHARINDEX in the WHERE clause is to eliminate tables that join to the circle. The WHERE clause on the SELECT is also to eliminate duplicates and supersets of the circular reference.

Currently we only get self referencing results.

Over time, the following changes were introduced:

alter table USState

  add foreign key (JurisdictionID)

  References Jurisdiction(JurisdictionID);

Alter table Country add JurisdictionID INT not null;

alter table Country

add foreign key (JurisdictionID)

References Jurisdiction(JurisdictionID);

Now the Diagram looks like this:

I hope this would never be designed this way; but, it is loosely based on recent events for an example.

Rerun the Circular reference query and the results are:

We can now see the table and the path with one or two hops that create the circular reference. The bigger the circle the harder it would be to determine visually.

I hope you don’t need this often; but, it will save you some analysis time if you do.