데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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/

 




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2052 Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화 jevida(강성욱) 2017.01.11 1576
2051 시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기 jevida(강성욱) 2017.01.11 2348
2050 In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고 jevida(강성욱) 2017.01.11 1277
2049 Spool 연산자와 추적 플래그 8690 jevida(강성욱) 2017.01.11 1856
2048 .NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작 jevida(강성욱) 2017.01.11 1595
2047 함수 통계 정보 확인 (sys.dm_exec_function_stats) jevida(강성욱) 2017.01.11 1566
2046 NULL 데이터가 포함된 데이터 사용 시 주의점 jevida(강성욱) 2017.01.11 2821
2045 통계정보와 실제 데이터 분포 확인하기 jevida(강성욱) 2017.01.11 1669
2044 SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류 jevida(강성욱) 2017.01.11 1457
2043 SSIS 실행 로그 남기기 jevida(강성욱) 2017.01.11 2664
2042 확장이벤트를 사용한 실행 계획 캡처 jevida(강성욱) 2017.01.11 1211
» 테이블 외래키 트리 확인 및 데이터 삭제하기 jevida(강성욱) 2017.01.11 1767
2040 기본 추적을 사용한 SQL Server 스키마 변경사항 캡처 jevida(강성욱) 2017.01.11 1552
2039 SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기 jevida(강성욱) 2017.01.11 1651
2038 SQL Server 임시 테이블 특성 jevida(강성욱) 2017.01.11 7134
2037 트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) jevida(강성욱) 2017.01.11 2152
2036 컬럼스토어 인덱스 성능 (Columnsotre Index Performance) jevida(강성욱) 2017.01.11 3184
2035 In-Memory 최적화 파일 경로 변경 jevida(강성욱) 2017.01.11 1495
2034 SA 계정 이름 변경 및 비활성화 jevida(강성욱) 2017.01.11 2272
2033 SQL Server 데이터베이스 속성을 확인할 때 사용되는 master.dbo.spt_values 복구 jevida(강성욱) 2017.01.11 1567





XE Login