Catch 22 or creating SQL dependency circle.

That is a kind of a SQL joke. Part of the fun being a DBA.

First case of SQL dependency circle I will demonstrate on two tables interdependent on each other.

Here is a script to create these simple tables and build dependency relationships to each other:

USE TestDB; GO CREATE TABLE tbl_Catch_1(     ID INT IDENTITY(1,1) PRIMARY KEY,     Catch_2 INT ); GO CREATE TABLE tbl_Catch_2(     ID INT IDENTITY(1,1) PRIMARY KEY,     Catch_1 INT ); GO ALTER TABLE tbl_Catch_1 ADD CONSTRAINT FK_Catch_1 FOREIGN KEY (Catch_2) REFERENCES tbl_Catch_2(ID); GO ALTER TABLE tbl_Catch_2 ADD CONSTRAINT FK_Catch_2 FOREIGN KEY (Catch_1) REFERENCES tbl_Catch_1(ID); GO

Now, if you try to delete any of these tables you will get an error kind of:

Msg 3726, Level 16, State 1, Line 78 Could not drop object 'tbl_Catch_1' because it is referenced by a FOREIGN KEY constraint. Msg 3726, Level 16, State 1, Line 80 Could not drop object 'tbl_Catch_2' because it is referenced by a FOREIGN KEY constraint.

It is very simple to resolve that issue by dropping one of the relationships first:

ALTER TABLE tbl_Catch_1 DROP CONSTRAINT FK_Catch_1; GO DROP TABLE tbl_Catch_2; GO DROP TABLE tbl_Catch_1; GO

The second SQL interdependency example will be little bit more complicated:

Here I create an interdependency between table an a function:

USE TestDB; GO CREATE TABLE tbl_Catch_3(ID INT IDENTITY(1,1)); GO CREATE FUNCTION dbo.fn_Catch_3() RETURNS INT WITH SCHEMABINDING AS BEGIN   RETURN (SELECT MAX(ID) + 1 FROM dbo.tbl_Catch_3); END; GO ALTER TABLE tbl_Catch_3 ADD Next_ID as dbo.fn_Catch_3(); GO

Try to see if table and function are functioning correctly:

BEGIN     INSERT INTO tbl_Catch_3 DEFAULT VALUES;     SELECT * FROM tbl_Catch_3     WHERE ID = (SELECT MAX(ID) FROM tbl_Catch_3); END GO 5 GO

Everything looks fine:

Now, if we try to delete the table or the function we get following messages:

Msg 3729, Level 16, State 1, Line 117 Cannot DROP FUNCTION 'dbo.fn_Catch_3' because it is being referenced by object 'tbl_Catch_3'. Msg 3729, Level 16, State 1, Line 119 Cannot DROP TABLE 'tbl_Catch_3' because it is being referenced by object 'fn_Catch_3'.

The easiest way to resolve that issue is to go backwards and delete computed column first:

ALTER TABLE tbl_Catch_3 DROP COLUMN Next_ID; GO DROP FUNCTION dbo.fn_Catch_3; GO DROP TABLE tbl_Catch_1; GO

I'm pretty sure there are many other ways to have SQL dependency circle, but these two are only I have faced.