Recently, I was tasked with writing a job that would delete data — with a bunch of rules on what could be deleted and what couldn’t. I thought it was going to be fairly straight forward, but the finer points of the situation made it quite complicated.
The Situation:
The diagram below works as an example of the situation. Sometimes I needed to delete data from Table1 as well as all other dependent data, sometimes I needed to delete it from Table3, sometimes from Table4, etc. I wrote the procedures to do delete each set of dependent data, but I was never sure if I had gotten it all – the real life situation wasn’t this linear and sometimes tests would fail and I would add another table to my deletion steps. I needed a script…
The Setup:
I decided to create the situation above and see if I could come up with a script that would identify PKs that would cause my deletions to fail.
Here’s the script:
CREATE TABLE [dbo].[Table1]( [T1ID] [int] NOT NULL, CONSTRAINT [T1ID_PK] PRIMARY KEY CLUSTERED ( [T1ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table9]( [T9ID] [int] NOT NULL, CONSTRAINT [T9ID_PK] PRIMARY KEY CLUSTERED ( [T9ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table3]( [T3ID] [int] NOT NULL, [T1ID] [int] NULL, [T9ID] [int] NULL, CONSTRAINT [T3ID_PK] PRIMARY KEY CLUSTERED ( [T3ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table2]( [T2ID] [int] NOT NULL, [T1ID] [int] NULL, CONSTRAINT [T2ID_PK] PRIMARY KEY CLUSTERED ( [T2ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table7]( [T7ID] [int] NOT NULL, [T3ID] [int] NULL, CONSTRAINT [T7ID_PK] PRIMARY KEY CLUSTERED ( [T7ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table6]( [T6ID] [int] NOT NULL, [T3ID] [int] NULL, CONSTRAINT [T6ID_PK] PRIMARY KEY CLUSTERED ( [T6ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table5]( [T5ID] [int] NOT NULL, [T2ID] [int] NULL, CONSTRAINT [T5ID_PK] PRIMARY KEY CLUSTERED ( [T5ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table4]( [T4ID] [int] NOT NULL, [T2ID] [int] NULL, CONSTRAINT [T4ID_PK] PRIMARY KEY CLUSTERED ( [T4ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go CREATE TABLE [dbo].[Table8]( [T8ID] [int] NOT NULL, [T4ID] [int] NULL, CONSTRAINT [T8ID_PK] PRIMARY KEY CLUSTERED ( [T8ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] Go ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [FK_Table2_Table1] FOREIGN KEY([T1ID]) REFERENCES [dbo].[Table1] ([T1ID]) Go ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table1] Go ALTER TABLE [dbo].[Table3] WITH CHECK ADD CONSTRAINT [FK_Table3_Table1] FOREIGN KEY([T1ID]) REFERENCES [dbo].[Table1] ([T1ID]) Go ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table1] Go ALTER TABLE [dbo].[Table3] WITH CHECK ADD CONSTRAINT [FK_Table3_Table9] FOREIGN KEY([T9ID]) REFERENCES [dbo].[Table9] ([T9ID]) Go ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table9] Go ALTER TABLE [dbo].[Table4] WITH CHECK ADD CONSTRAINT [FK_Table4_Table2] FOREIGN KEY([T2ID]) REFERENCES [dbo].[Table2] ([T2ID]) Go ALTER TABLE [dbo].[Table4] CHECK CONSTRAINT [FK_Table4_Table2] Go ALTER TABLE [dbo].[Table5] WITH CHECK ADD CONSTRAINT [FK_Table5_Table2] FOREIGN KEY([T2ID]) REFERENCES [dbo].[Table2] ([T2ID]) Go ALTER TABLE [dbo].[Table5] CHECK CONSTRAINT [FK_Table5_Table2] Go ALTER TABLE [dbo].[Table6] WITH CHECK ADD CONSTRAINT [FK_Table6_Table3] FOREIGN KEY([T3ID]) REFERENCES [dbo].[Table3] ([T3ID]) Go ALTER TABLE [dbo].[Table6] CHECK CONSTRAINT [FK_Table6_Table3] Go ALTER TABLE [dbo].[Table7] WITH CHECK ADD CONSTRAINT [FK_Table7_Table3] FOREIGN KEY([T3ID]) REFERENCES [dbo].[Table3] ([T3ID]) Go ALTER TABLE [dbo].[Table7] CHECK CONSTRAINT [FK_Table7_Table3] Go ALTER TABLE [dbo].[Table8] WITH CHECK ADD CONSTRAINT [FK_Table8_Table4] FOREIGN KEY([T4ID]) REFERENCES [dbo].[Table4] ([T4ID]) Go ALTER TABLE [dbo].[Table8] CHECK CONSTRAINT [FK_Table8_Table4] Go
The Script:
I looked around the old information super highway and found Pinal Dave (Blog/Twitter) had something that fit into what I was looking for here:
I tweaked it and used the ABCs of CTEs (from Jason Strate (Blog/Twitter) and came up with the following script to identify tables which have primary keys to the initial table, so you will never forget a table in a deletion again :
DECLARE @TableName VARCHAR(255) = 'Table1' --Change this to find the different situations. ;WITH FKStructure (K_Table, FK_Column, PK_Table, PK_Column, Constraint_Name, Level) as (-- Anchor member definition SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME, 1 AS Level 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 WHERE PK.TABLE_NAME= @TableName UNION ALL -- Recursive member definition SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME, Level + 1 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 JOIN FKStructure FKS on FKS.K_Table = PK.TABLE_NAME) Select * from FKStructure