Share via

  • 아티클

연계 참조 무결성 제약 조건

연계 참조 무결성 제약 조건을 사용하면 기존 외래 키가 가리키는 키를 사용자가 삭제 또는 업데이트하려 할 때 SQL Server에서 수행할 작업을 정의할 수 있습니다.

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를 지정할 수 없습니다.

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

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

    [!참고]

    timestamp 열이 외래 키 또는 참조되는 키의 일부인 경우에는 CASCADE를 지정할 수 없습니다.

  • ON DELETE SET NULL
    다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 행에서 참조되는 외래 키를 구성하는 모든 값이 Null로 설정되도록 지정합니다. 이 제약 조건을 실행하려면 대상 테이블의 모든 외래 키 열에서 Null이 허용되어야 합니다.

  • ON UPDATE SET NULL
    다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 업데이트하려고 하면 해당 행에서 참조되는 외래 키를 구성하는 모든 값이 Null로 설정되도록 지정합니다. 이 제약 조건을 실행하려면 대상 테이블의 모든 외래 키 열에서 Null이 허용되어야 합니다.

  • ON DELETE SET DEFAULT
    다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 행에서 참조되는 외래 키를 구성하는 모든 값이 기본값으로 설정되도록 지정합니다. 이 제약 조건을 실행하려면 대상 테이블의 모든 외래 키 열에 DEFAULT 정의가 있어야 합니다. 열에 Null이 허용되고 명시적 기본값이 설정되어 있지 않은 경우 NULL은 해당 열의 암시적 기본값이 됩니다. ON DELETE SET DEFAULT에 의해 Null이 아닌 값이 설정된 경우 외래 키 제약 조건의 유효성을 유지하려면 기본 테이블에 해당 값이 있어야 합니다.

  • ON UPDATE SET DEFAULT
    다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 업데이트하려고 하면 해당 행에서 참조되는 외래 키를 구성하는 모든 값이 기본값으로 설정되도록 지정합니다. 이 제약 조건을 실행하려면 대상 테이블의 모든 외래 키 열에 DEFAULT 정의가 있어야 합니다. 열에 Null이 허용되고 명시적 기본값이 설정되어 있지 않은 경우 NULL은 해당 열의 암시적 기본값이 됩니다. ON UPDATE SET DEFAULT에 의해 Null이 아닌 값이 설정된 경우 외래 키 제약 조건의 유효성을 유지하려면 기본 테이블에 해당 값이 있어야 합니다.

AdventureWorks2008R2의 Purchasing.ProductVendor 테이블에서 FK_ProductVendor_Vendor_VendorID 제약 조건을 고려해 봅니다. 이 제약 조건은 ProductVendor 테이블의 VendorID 열과 Purchasing.Vendor 테이블의 VendorID 기본 키 열의 외래 키 관계를 설정합니다. 제약 조건에 ON DELETE CASCADE를 지정하는 경우 Vendor 테이블에서 VendorID가 100인 행을 삭제하면 ProductVendor 테이블에서 VendorID가 100인 세 개의 행도 삭제됩니다. 제약 조건에 ON UPDATE CASCADE를 지정하는 경우 Vendor 테이블에서 VendorID 값을 100에서 155로 업데이트하면 ProductVendor의 세 개의 행에서 VendorID 값이 현재 100인 VendorID 값도 업데이트됩니다.

INSTEAD OF DELETE 트리거가 있는 테이블에는 ON DELETE CASCADE를 지정할 수 없습니다. INSTEAD OF UPDATE 트리거가 있는 테이블에 대해서는 ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL 및 ON UDATE SET DEFAULT를 지정할 수 없습니다.

다중 연계 동작

개별적인 DELETE 또는 UPDATE 문은 일련의 연계 참조 작업을 시작할 수 있습니다. 예를 들어 TableA, TableB 및 TableC라는 3개의 테이블이 포함되어 있는 데이터베이스를 고려해 봅니다. TableA의 기본 키에 대해 TableB의 외래 키가 ON DELETE CASCADE로 정의되어 있습니다. 또한 TableB의 기본 키에 대해 TableC의 외래 키가 ON DELETE CASCADE로 정의되어 있습니다. DELETE 문에 의해 TableA에 있는 행이 삭제되면 TableA의 삭제된 기본 키와 일치하는 외래 키가 있는 TableB의 행도 삭제되고 TableB의 삭제된 기본 키와 일치하는 외래 키가 있는 TableC의 행도 삭제됩니다.

하나의 DELETE 또는 UPDATE 문에 의해 시작된 일련의 연계 참조 작업은 순환 참조가 없는 트리를 만듭니다. DELETE 또는 UPDATE 문으로 수행되는 모든 연계 참조 작업의 목록에 테이블이 한 번 이상 나타나서는 안 됩니다. 또한 연계 참조 작업의 트리에는 지정된 테이블에 대한 경로가 하나 이상 있어서는 안 됩니다. 트리 분기는 NO ACTION이 지정되어 있거나 기본값인 테이블을 발견하면 종료됩니다.

트리거 및 연계 참조 작업

연계 참조 작업은 다음과 같은 방식으로 AFTER UPDATE 또는 AFTER DELETE 트리거를 시작합니다.

원래 DELETE 또는 UPDATE 문에 의해 직접적으로 시작된 모든 연계 참조 작업이 먼저 수행됩니다.

영향을 받는 테이블에 AFTER 트리거가 정의되어 있는 경우 해당 트리거는 모든 연계 작업이 수행된 후에 시작됩니다. 이 트리거는 연계 작업 순서와 반대로 시작됩니다. 단일 테이블에 여러 트리거가 있는 경우 이 테이블에 첫 번째 또는 마지막으로 지정된 트리거가 없다면 임의의 순서로 시작됩니다. 이 시작 순서는 sp_settriggerorder를 사용하여 지정한 대로 수행됩니다.

UPDATE 또는 DELETE 작업의 직접적인 대상인 테이블로부터 여러 연계 체인이 시작되는 경우 이 체인이 각 트리거를 시작하는 순서는 지정되지 않습니다. 그러나 항상 한 체인이 해당 트리거를 모두 시작한 후에 다른 체인이 해당 트리거를 시작합니다.

UPDATE 또는 DELETE 작업의 직접적인 대상인 테이블에 대한 AFTER 트리거는 영향을 받는 행이 있는지 여부에 관계 없이 항상 시작됩니다. 이 경우 다른 테이블은 연계 작업에 영향을 받지 않습니다.

이전 트리거 중 하나가 다른 테이블에 대해 UPDATE 또는 DELETE 작업을 수행하는 경우 이 작업에 의해 보조 연계 체인이 시작될 수 있습니다. 이러한 보조 체인은 기본 체인의 모든 트리거가 시작된 후에 각 UPDATE 또는 DELETE 작업에서 처리됩니다. 이러한 과정은 나중에 수행되는 UPDATE 또는 DELETE 작업에서 재귀적으로 반복될 수 있습니다.

트리거 내에서 CREATE, ALTER, DELETE 또는 기타 DDL(데이터 정의 언어) 작업이 수행되면 DDL 트리거가 시작될 수 있습니다. 그리고 추가 연계 체인과 트리거를 시작하는 DELETE 또는 UPDATE 작업이 뒤이어 수행될 수 있습니다.

특정 연계 참조 작업 체인 내에서 오류가 생성되면 오류가 발생하고 해당 체인에서 AFTER 트리거가 시작되지 않으며 체인을 만든 DELETE 또는 UPDATE 작업이 롤백됩니다.

INSTEAD OF 트리거가 있는 테이블은 연계 작업을 지정하는 REFERENCES 절도 가질 수 없습니다. 그러나 연계 작업의 대상이 되는 테이블의 AFTER 트리거는 다른 테이블 또는 그 개체에 정의된 INSTEAD OF 트리거를 시작하는 뷰에서 INSERT, UPDATE 또는 DELETE 문을 실행할 수 있습니다.

연계 참조 제약 조건 카탈로그 정보

sys.foreign_keys 카탈로그 뷰에 대해 쿼리를 실행하면 외래 키에 지정된 연계 참조 제약 조건을 나타내는 다음 값이 반환됩니다.

값

설명

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

sp_fkeys 및 sp_foreignkeys에 의해 반환된 UPDATE_RULE 및 DELETE_RULE 열은 CASCADE, SET NULL 또는 SET DEFAULT가 지정되어 있는 경우에 0을 반환하고 NO ACTION이 지정되어 있거나 기본값인 경우에는 1을 반환합니다.

외래 키가 sp_help의 개체로 지정되면 결과 집합에는 다음과 같은 열이 포함됩니다.

열 이름

데이터 형식

설명

delete_action

nvarchar(9)

삭제 작업이 CASCADE, SET NULL, SET DEFAULT, NO ACTION 또는 N/A(해당 없음)인지 나타냅니다.

update_action

nvarchar(9)

업데이트 작업이 CASCADE, SET NULL, SET DEFAULT, NO ACTION 또는 N/A(해당 없음)인지 나타냅니다.