SQL Server doesn’t mind circular dependencies in tables. That is, unless you have two tables which refer to each other, and try to add foreign key constraints to both.
Then you’ll see this (infamous) message:
Introducing FOREIGN KEY constraint 'FK_Foo' on table 'Blah' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
This message can mean a couple of things; one of them is two ways a deletion can cascade down to the same table (i.e. delete table T, and there are two foreign key cascade deletions to table D). The other is that you’re trying to add foreign keys in a way that causes a cycle, i.e. it will cascade back to the same table.
Thankfully, Wayne Berry wrote a script to take care of this. It finds and prints out cyclical dependencies, like so:
dba.Foo -> dba.Bar -> dba.Foo
You can find the full script below.
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
