Cascading Referential Integrity Constraint

연계 참조 무결성 제약 조건 (Cascading Referential Integrity Constraint)

FOREIGN KEY 제약 조건이 적용된 컬럼들에 대한 데이터 변경이 자주 있는 경우, 이러한 데이터 변경을 처리 하기 위해서는 트리거나 저장 프로시저, 배치 쿼리와 같은 형식으로 관계된 모든 테이블을 처리해야만 하였다. 하지만 SQL 2005부터 새롭게 추가된 기능인 연계 참조 무결성 제약 조건을 통하여 외부키가 잡힌 데이터의 수정 혹은 삭제시 관련된 테이블의 데이터를 동시에 삭제하거나 수정이 가능 하게 되었다.

 

CREATE TABLE 및 ALTER TABLE 문의 REFERENCES 절에는 ON DELETE 및 ON UPDATE 절을 사용할 수 있고 외래 키 관계 대화 상자를 사용하여 연계 작업을 정의할 수도 있다.

 

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

 

ON DELETE 또는 ON UPDATE를 지정하지 않으면 NO ACTION이 기본값이 되며 ON DELETE NO ACTION는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 오류가 발생하고 DELETE 문이 롤백된다. ON UPDATE NO ACTION는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행에서 키 값을 업데이트하려고 하면 오류가 발생하고 UPDATE 문이 롤백된다.

 

CASCADE, SET NULL 및 SET DEFAULT를 사용하면 내용이 수정된 테이블로 역추적할 수 있는 외래 키 관계를 갖도록 정의된 테이블에 키 값의 삭제 또는 업데이트가 적용되도록 할 수 있으며 대상 테이블에서도 연계 참조 작업이 정의되어 있다면 테이블에서 삭제 또는 업데이트된 행에 대해서도 지정된 연계동작이 적용된다. 예외로 timestamp열이 있는 외래키나 기본키에는 CASCADE를 지정할 수 없으며 Instead If 트리거가 존재 하는 테이블에 대해서도 지정할수 없다.  

 

ON DELETE CASCADE는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제가 된다.

 

ON UPDATE CASCADE는 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키 값이 포함된 행에서 키 값을 업데이트하려고 하면 해당 외래 키를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트된다. .

 

그럼 이제 어떤식으로 사용되는지 알아 보도록 하자.

 

CASCADE 옵션

 

CREATE TABLE [마미테이블_CASCADE]

(

       [컬럼] INT PRIMARY KEY,

       [컬럼] VARCHAR(20)

)

GO

CREATE TABLE [자식테이블_CASCADE]

(

       [컬럼] INT IDENTITY(1,1),

       [컬럼] INT

)

GO

 

ALTER TABLE [자식테이블_CASCADE]

       ADD CONSTRAINT [포린키_캐스캐이드테스트_CASCADE]

       FOREIGN KEY ([컬럼]) REFERENCES [마미테이블_CASCADE]([컬럼])

             ON DELETE CASCADE

             ON UPDATE CASCADE         

GO

 

--ALTER TABLE [자식테이블_CASCADE] DROP  CONSTRAINT [포린키_캐스캐이드테스트_CASCADE]

 

INSERT [마미테이블_CASCADE] VALUES(1,'테스트')

INSERT [마미테이블_CASCADE] VALUES(2,'테스트')

INSERT [마미테이블_CASCADE] VALUES(3,'테스트')

INSERT [마미테이블_CASCADE] VALUES(4,'테스트')

INSERT [마미테이블_CASCADE] VALUES(5,'테스트')

 

SELECT * FROM [마미테이블_CASCADE]

 

INSERT [자식테이블_CASCADE](컬럼) VALUES(1)

INSERT [자식테이블_CASCADE](컬럼) VALUES(1)

INSERT [자식테이블_CASCADE](컬럼) VALUES(2)

INSERT [자식테이블_CASCADE](컬럼) VALUES(2)

INSERT [자식테이블_CASCADE](컬럼) VALUES(3)

INSERT [자식테이블_CASCADE](컬럼) VALUES(3)

INSERT [자식테이블_CASCADE](컬럼) VALUES(3)

INSERT [자식테이블_CASCADE](컬럼) VALUES(4)

INSERT [자식테이블_CASCADE](컬럼) VALUES(5)

INSERT [자식테이블_CASCADE](컬럼) VALUES(5)

 

 

SELECT * FROM [마미테이블_CASCADE]

SELECT * FROM [자식테이블_CASCADE]

 

DELETE FROM [마미테이블_CASCADE]  WHERE [컬럼] = 1

-- 부모테이블에서삭제를하면자식테이블도영향을받는다.

SELECT * FROM [마미테이블_CASCADE]

SELECT * FROM [자식테이블_CASCADE]

 

DELETE FROM [자식테이블_CASCADE]  WHERE [컬럼] = 2

-- 자식테이블에서삭제를하면부모테이블만영향을받지않는다.

SELECT * FROM [마미테이블_CASCADE]

SELECT * FROM [자식테이블_CASCADE]

 

UPDATE [마미테이블_CASCADE] SET [컬럼] = 33 WHERE [컬럼] = 3

SELECT * FROM [마미테이블_CASCADE]

SELECT * FROM [자식테이블_CASCADE]

-- 부모테이블에서수정을하면자식테이블도영향을받는다.

 

UPDATE [자식테이블_CASCADE] SET [컬럼] = 44 WHERE [컬럼] = 4

-- 자식테이블에서수정으로제약조건에따라오류가발생한다.

 

NO ACTION 옵션

CREATE TABLE [마미테이블_NOACTION]

(

       [컬럼] INT PRIMARY KEY,

       [컬럼] VARCHAR(20)

)

GO

CREATE TABLE [자식테이블_NOACTION]

(

       [컬럼] INT IDENTITY(1,1),

       [컬럼] INT

)

GO

 

ALTER TABLE [자식테이블_NOACTION]

       ADD CONSTRAINT [포린키_캐스캐이드테스트_NOACTION]

       FOREIGN KEY ([컬럼]) REFERENCES [마미테이블_NOACTION]([컬럼])

             ON DELETE NO ACTION

             ON UPDATE NO ACTION

GO

 

ALTER TABLE [자식테이블_NOACTION] DROP  CONSTRAINT [포린키_캐스캐이드테스트_NOACTION]

 

INSERT [마미테이블_NOACTION] VALUES(1,'테스트')

INSERT [마미테이블_NOACTION] VALUES(2,'테스트')

INSERT [마미테이블_NOACTION] VALUES(3,'테스트')

INSERT [마미테이블_NOACTION] VALUES(4,'테스트')

INSERT [마미테이블_NOACTION] VALUES(5,'테스트')

 

SELECT * FROM [마미테이블_NOACTION]

 

INSERT [자식테이블_NOACTION](컬럼) VALUES(1)

INSERT [자식테이블_NOACTION](컬럼) VALUES(1)

INSERT [자식테이블_NOACTION](컬럼) VALUES(2)

INSERT [자식테이블_NOACTION](컬럼) VALUES(2)

INSERT [자식테이블_NOACTION](컬럼) VALUES(3)

INSERT [자식테이블_NOACTION](컬럼) VALUES(3)

INSERT [자식테이블_NOACTION](컬럼) VALUES(3)

INSERT [자식테이블_NOACTION](컬럼) VALUES(4)

INSERT [자식테이블_NOACTION](컬럼) VALUES(5)

INSERT [자식테이블_NOACTION](컬럼) VALUES(5)

 

SELECT * FROM [자식테이블_NOACTION]

 

DELETE FROM [마미테이블_NOACTION]  WHERE [컬럼] = 1

-- 자식테이블에데이터가있기때문에제약조건에따라오류가발생한다.

 

 

DELETE FROM [자식테이블_NOACTION]  WHERE [컬럼] = 1

 

DELETE FROM [마미테이블_NOACTION]  WHERE [컬럼] = 1

 

SELECT * FROM [마미테이블_NOACTION]

SELECT * FROM [자식테이블_NOACTION]

 

다중 CASCADE 옵션

 

CREATE TABLE [다중부모_CASCADE]

(

       [컬럼] INT PRIMARY KEY,

       [컬럼] VARCHAR(20)

)

GO

CREATE TABLE [다중자식_CASCADE]

(

       [컬럼] INT PRIMARY KEY,

       [컬럼] INT

)

GO

 

CREATE TABLE [다중손자_CASCADE]

(

       [컬럼] INT PRIMARY KEY,

       [컬럼] INT

)

GO

 

ALTER TABLE [다중자식_CASCADE]

       ADD CONSTRAINT [포린키_캐스트]

       FOREIGN KEY ([컬럼]) REFERENCES [다중부모_CASCADE]([컬럼])

             ON DELETE CASCADE

             ON UPDATE CASCADE         

GO

 

ALTER TABLE [다중손자_CASCADE]

       ADD CONSTRAINT [포린키_다중캐스트]

       FOREIGN KEY ([컬럼]) REFERENCES [다중자식_CASCADE]([컬럼])

             ON DELETE CASCADE

             ON UPDATE CASCADE         

GO

 

INSERT [다중부모_CASCADE] VALUES(1,'테스트')

INSERT [다중부모_CASCADE] VALUES(2,'테스트')

INSERT [다중부모_CASCADE] VALUES(3,'테스트')

 

SELECT * FROM [다중부모_CASCADE]

 

INSERT [다중자식_CASCADE] VALUES(1,1)

INSERT [다중자식_CASCADE] VALUES(2,2)

INSERT [다중자식_CASCADE] VALUES(3,3)

 

INSERT [다중손자_CASCADE] VALUES(1,1)

INSERT [다중손자_CASCADE] VALUES(2,1)

INSERT [다중손자_CASCADE] VALUES(3,2)

INSERT [다중손자_CASCADE] VALUES(4,3)

 

 

DELETE FROM [다중부모_CASCADE] WHERE [컬럼] = 1