Friday, February 20, 2015

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.

No comments:

Post a Comment