테이블 외래키 트리 확인 및 데이터 삭제하기

테이블 외래키 트리 확인 및 데이터 삭제하기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

SQL Server에서 데이터 무결성을 유지하기 위해 생성한 외래키 제약조건이 있는 경우 데이터를 삭제 할 때 제약 조건에 위배되지 않도록 순차적으로 작업을 해야 한다. 테이블에 생성되어있는 외래키 연결에 대해 트리 구조로 살펴보고 순차적으로 데이터를 삭제할 수 있는 방법에 대해서 알아본다.

 

실습을 위해 테이블을 생성한다. 스크립트를 실행 하면 그림과 같은 외래키로 구성된 테이블이 생성된다.

-- sample tables with FKs create table dbo.M (id int primary key, M_c1 int not null, M_c2 datetime not null, M_c3 char(10) not null, M_c4 datetime); create unique index ux1_A on dbo.M (M_c1); create unique index ux2_A on dbo.M (M_c2, M_c3);   create table dbo.N1 (id int primary key, N1_c1 int, N1_c2 datetime, N1_c3 char(10), N1_c4 datetime); create unique index ux1_N1 on dbo.N1 (N1_c1, N1_c3); create unique index ux2_N1 on dbo.N1 ( N1_c2); alter table dbo.N1 add constraint FK_N1_M foreign key (N1_c1) references dbo.M (M_c1);   create table dbo.N2 (id int primary key, N2_c1 int, N2_c2 datetime, N2_c3 char(10), N2_c4 datetime); create unique index ux1_N2 on dbo.N2 (N2_c1); create unique index ux2_N2 on dbo.N2 (N2_c3, N2_c4); alter table dbo.N2 add constraint FK1_N2_M foreign key (N2_c2, N2_c3) references dbo.M (M_c2, M_c3);   create table dbo.P1 (id int primary key, P1_c1 int, P1_c2 datetime, P1_c3 char(10), P1_c4 datetime); create unique index ux1_P1 on dbo.P1 (P1_c1); alter table dbo.P1 add constraint FK_P1_N1 foreign key (P1_c1, P1_c3) references dbo.N1 (N1_c1, N1_c3);   create table dbo.P2 (id int primary key, P2_c1 int, P2_c2 datetime, P2_c3 char(10), P2_c4 datetime); create unique index ux1_P2 on dbo.P2 (P2_c1); create unique index ux2_P2 on dbo.P2 (P2_c2, P2_c3); alter table dbo.P2 add constraint FK_P2_N1 foreign key (P2_c2) references dbo.N1 (N1_c2); alter table dbo.P2 add constraint FK_P2_N2 foreign key (P2_c3, P2_c4) references dbo.N2 (N2_c3, N2_c4);   create table dbo.P3 (id int primary key, P3_c1 int, P3_c2 datetime, P3_c3 char(10), P3_c4 datetime); alter table dbo.P3 add constraint FK_P3_N2 foreign key (P3_c1) references dbo.N2 (id);   create table dbo.Q1 (id int primary key, Q1_c1 int, Q1_c2 datetime, Q1_c3 char(10), Q1_C4 datetime); alter table dbo.Q1 add constraint FK_Q1_P1 foreign key (Q1_c1) references dbo.P1 (P1_c1);   create table dbo.Q2 (id int primary key, Q2_c1 int, Q2_c2 datetime, Q2_c3 char(10), Q2_c4 datetime); alter table dbo.Q2 add constraint FK_Q2_P2 foreign key (Q2_c1) references dbo.P2 (id);   create table dbo.Q3 (id int primary key, Q3_c1 int, Q3_c2 datetime, Q3_c3 char(10), Q3_c4 datetime); alter table dbo.Q3 add constraint FK1_Q3_N2 foreign key (Q3_c1) references dbo.N2 (id); alter table dbo.Q3 add constraint FK2_Q3_P2 foreign key (Q3_c2, Q3_c3) references dbo.P2 (P2_c2, P2_c3); GO   -- populate all tables insert into dbo.M (id, M_c1, M_c2, M_c3, M_c4) select 1, 10, '2015-01-01', 'AB1', '2015-01-02' union all select 2, 20, '2015-01-02', 'AB2', '2015-01-03' union all select 3, 30, '2015-01-03', 'AB3', '2015-01-04';   insert into dbo.N1 (id, N1_c1, N1_c2, N1_c3, N1_c4) select 11, 20, '2015-01-01', 'CD1', '2015-01-02' union all select 21, 30, '2015-01-02', 'CD2', '2015-01-03' union all select 31, 10, '2015-01-03', 'CD3', '2015-01-04';   insert into dbo.N2 (id, N2_c1, N2_c2, N2_c3, N2_c4) select 11, 11, '2015-01-01', 'AB1', '2015-01-02' union all select 12, 22, '2015-01-02', 'AB2', '2015-01-03' union all select 13, 33, '2015-01-03', 'AB3', '2015-01-04';   insert into dbo.P1 (id, P1_c1, P1_c2, P1_c3, P1_c4) select 100, 20, '2014-01-01', 'CD1', '2015-01-02' union all select 101, 30, '2014-01-02', 'CD2', '2015-01-03' union all select 102, 10, '2014-01-03', 'CD3', '2015-01-04'   insert into dbo.P2 (id, P2_c1, P2_c2, P2_c3, P2_c4) select 200, 20, '2015-01-01', 'AB1', '2015-01-02' union all select 201, 30, '2015-01-02', 'AB2', '2015-01-03' union all select 202, 10, '2015-01-03', 'AB3', '2015-01-04'   insert into dbo.P3 (id, P3_c1, P3_c2, P3_c3, P3_c4) select 301, 11, '2010-01-01', 'EF1', '2015-02-02' union all select 302, 13, '2010-01-02', 'EF2', '2015-02-03' union all select 303, 12, '2010-01-03', 'EF3', '2015-02-04'

 

 

아래 스크립트는 테이블의 외래키 연결을 트리 구조로 나타내는 프로시저를 생성한다.

use SW_TEST go   if object_id('dbo.usp_searchFK', 'P') is not null     drop proc dbo.usp_SearchFK; go create proc dbo.usp_SearchFK @table varchar(256) -- use two part name convention , @lvl int=0 -- do not change , @ParentTable varchar(256)='' -- do not change , @debug bit = 1 as begin     set nocount on;     declare @dbg bit;     set @dbg=@debug;     if object_id('tempdb..#tbl', 'U') is null         create table #tbl (id int identity, tablename varchar(256), lvl int, ParentTable varchar(256));     declare @curS cursor;     if @lvl = 0         insert into #tbl (tablename, lvl, ParentTable)         select @table, @lvl, Null;     else         insert into #tbl (tablename, lvl, ParentTable)         select @table, @lvl,@ParentTable;     if @dbg=1             print replicate('----', @lvl) + 'lvl ' + cast(@lvl as varchar(10)) + ' = ' + @table;          if not exists (select * from sys.foreign_keys where referenced_object_id = object_id(@table))         return;     else     begin -- else         set @ParentTable = @table;         set @curS = cursor for         select tablename=object_schema_name(parent_object_id)+'.'+object_name(parent_object_id)         from sys.foreign_keys         where referenced_object_id = object_id(@table)         and parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;           open @curS;         fetch next from @curS into @table;           while @@fetch_status = 0         begin --while             set @lvl = @lvl+1;             -- recursive call             exec dbo.usp_SearchFK @table, @lvl, @ParentTable, @dbg;             set @lvl = @lvl-1;             fetch next from @curS into @table;         end --while         close @curS;         deallocate @curS;     end -- else     if @lvl = 0         select * from #tbl;     return; end go

 

 

외래키 트리 구조를 나타내는 프로시저를 실행한다. 프로시저는 소유자를 포함한 테이블명을 사용한다. 결과 탭에서 메시지의 내용을 보면 부모 테이블에서 마지막 자식 테이블까지 외래키로 연결된 구조를 트리 구조로 확인할 수 있다.

exec dbo.usp_SearchFK 'dbo.M'

 

 

아래 스크립트는 트리 구조로 연결된 테이블에서 순차적으로 데이터를 삭제할 수 있도록 삭제 스크립트를 생성해주는 스크립트이다. 결과 탭에서 출력해주는 스크립트를 다른 SSMS창에서 실행하면 데이터 삭제가 가능하다.

if object_id('tempdb..#tmp') is not null     drop table #tmp; create table #tmp (id int, tablename varchar(256), lvl int, ParentTable varchar(256));   insert into #tmp exec dbo.usp_SearchFK @table='dbo.M', @debug=0;   declare @where varchar(max) ='where M.id=2' -- if @where clause is null or empty, it will delete tables as a whole with the right order declare @curFK cursor, @fk_object_id int; declare @sqlcmd varchar(max)='', @crlf char(2)=char(0x0d)+char(0x0a); declare @child varchar(256), @parent varchar(256), @lvl int, @id int; declare @i int; declare @t table (tablename varchar(128)); declare @curT cursor; if isnull(@where, '')= '' begin     set @curT = cursor for select tablename, lvl from #tmp order by lvl desc     open @curT;     fetch next from @curT into @child, @lvl;     while @@fetch_status = 0     begin -- loop @curT         if not exists (select 1 from @t where tablename=@child)             insert into @t (tablename) values (@child);         fetch next from @curT into @child, @lvl;     end -- loop @curT     close @curT;     deallocate @curT;       select @sqlcmd = @sqlcmd + 'delete from ' + tablename + @crlf from @t ;     print @sqlcmd; end else begin     declare curT cursor for     select lvl, id     from #tmp     order by lvl desc;       open curT;     fetch next from curT into @lvl, @id;     while @@FETCH_STATUS =0     begin         set @i=0;         if @lvl =0         begin -- this is the root level             select @sqlcmd = 'delete from ' + tablename from #tmp where id = @id;         end -- this is the roolt level           while @i < @lvl         begin -- while               select top 1 @child=TableName, @parent=ParentTable from #tmp where id <= @id-@i and lvl <= @lvl-@i order by lvl desc, id desc;             set @curFK = cursor for             select object_id from sys.foreign_keys             where parent_object_id = object_id(@child)             and referenced_object_id = object_id(@parent)               open @curFK;             fetch next from @curFk into @fk_object_id             while @@fetch_status =0             begin -- @curFK                   if @i=0                     set @sqlcmd = 'delete from ' + @child + @crlf +                     'from ' + @child + @crlf + 'inner join ' + @parent ;                 else                     set @sqlcmd = @sqlcmd + @crlf + 'inner join ' + @parent ;                   ;with c as                 (                     select child = object_schema_name(fc.parent_object_id)+'.' + object_name(fc.parent_object_id), child_col=c.name                     , parent = object_schema_name(fc.referenced_object_id)+'.' + object_name(fc.referenced_object_id), parent_col=c2.name                     , rnk = row_number() over (order by (select null))                     from sys.foreign_key_columns fc                     inner join sys.columns c                     on fc.parent_column_id = c.column_id                     and fc.parent_object_id = c.object_id                     inner join sys.columns c2                     on fc.referenced_column_id = c2.column_id                     and fc.referenced_object_id = c2.object_id                     where fc.constraint_object_id=@fk_object_id                 )                     select @sqlcmd =@sqlcmd + case rnk when 1 then ' on ' else ' and ' end                     + @child +'.'+ child_col +'=' + @parent +'.' + parent_col                     from c;                     fetch next from @curFK into @fk_object_id;             end --@curFK             close @curFK;             deallocate @curFK;             set @i = @i +1;         end --while         print @sqlcmd + @crlf + @where + ';';         print '';         fetch next from curT into @lvl, @id;     end     close curT;     deallocate curT; end   go

 

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4059/script-to-delete-data-from-sql-server-tables-with-foreign-key-constraints/

 

2015-10-26 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MS SQL, 외래키, 제약조건, 외래키 테이블 삭제, 외래키 구조 보기, Foreign Key Constraint,