SQL에서 외래 키 제약 조건을 만드는 방법

사업체를 운영하고 계신다면, 데이터의 중요성과 필요성을 이미 체감하셨을 것입니다. 데이터를 저장하고 조작할 수 있는 효과적인 방법이 있다면 비즈니스 가치를 더욱 높일 수 있습니다.

데이터베이스는 특정 규칙에 따라 체계화되며, 그중에서도 관계형 데이터베이스는 1970년대부터 데이터 관리의 중요한 형태로 자리매김했습니다. 관계형 데이터베이스는 데이터를 구조화하는 데 유용하며, 특히 현대 시장에서는 데이터 조작 시 그 기능성으로 인해 널리 선호됩니다.

다양한 관계형 데이터베이스가 존재하지만, MySQL은 2022년 1월 Statista 자료에 따르면 세계 2위를 차지하며 업계 선두를 달리고 있습니다.

SQL 서버에서 제약 조건은 하나 이상의 열에 적용되는 미리 정의된 규칙과 제한 사항을 의미합니다. 이는 열의 값과 연결되어 특정 열 데이터의 완전성, 정확성 및 신뢰성을 유지하는 데 도움을 줍니다.

간단히 말해, 설정된 제약 규칙을 만족하는 데이터만 해당 열에 성공적으로 삽입될 수 있습니다. 데이터가 기준에 부합하지 않으면 삽입 작업은 중단됩니다.

이 글에서는 관계형 데이터베이스, 특히 MySQL에 대한 기본적인 이해를 가지고 있으며, 이 분야의 지식을 더욱 확장하고자 하는 분들을 대상으로 합니다. 특히 외래 키 제약 조건과 효과적으로 상호 작용하기 위한 몇 가지 유용한 팁을 공유하고자 합니다.

기본 키 제약 조건 – 핵심 요약

SQL 테이블에는 시스템 내 각 행을 고유하게 식별하는 하나 이상의 키 열이 있습니다. 테이블의 기본 키(PK)라고 불리는 이 열은 테이블의 개체 무결성을 강화하는 중요한 역할을 합니다. 기본 키 제약 조건은 데이터의 고유성을 보장하며, 일반적으로 ID 열에 설정됩니다.

테이블에 기본 키 제약 조건을 설정하면 데이터베이스 엔진은 각 기본 키 열에 대해 고유 인덱스를 자동으로 생성하여 데이터 고유성을 확보합니다. 또한, 기본 키는 쿼리 실행 시 빠른 데이터 접근을 제공하여 큰 이점을 가져다줍니다.

만약 기본 키 제약 조건이 여러 열에 걸쳐 정의된다면, 이는 복합 기본 키라고 불립니다. 이 경우 각 기본 키 열은 중복된 값을 가질 수 있지만, 기본 키를 구성하는 모든 열의 조합은 반드시 고유해야 합니다.

예를 들어, `id`, `names`, 그리고 `age` 열을 가진 테이블이 있다고 가정해 봅시다. `id`와 `names` 열 조합에 기본 키 제약 조건을 설정하면, `id` 또는 `names` 열 각각은 중복된 값을 가질 수 있지만, `id`와 `names`의 조합은 고유해야 합니다. 따라서 `id=1, name=Walter, age=22`와 `id=1, name=Henry, age=27`인 레코드는 허용되지만, `id=1, name=Walter` 조합은 중복되므로 다른 레코드를 추가할 수 없습니다.

다음은 기본 키 제약 조건에 대해 알아야 할 중요한 사항들입니다:

테이블은 단 하나의 기본 키 제약 조건만을 가질 수 있습니다.

기본 키는 최대 16개 열을 포함할 수 있으며, 최대 길이는 900자입니다.

기본 키로 생성된 인덱스는 테이블의 인덱스 수를 늘릴 수 있습니다. 테이블당 클러스터형 인덱스는 1개만 가능하며, 비클러스터형 인덱스는 999개로 제한됩니다.

키 제약 조건에 클러스터형 또는 비클러스터형이 명시되지 않은 경우, 클러스터형 인덱스가 기본적으로 사용됩니다.

기본 키 제약 조건에 선언된 모든 열은 NULL 값을 허용하지 않아야 합니다. 그렇지 않은 경우, 제약 조건과 연결된 모든 열은 자동으로 NOT NULL로 설정됩니다.

CLR(공용 언어 런타임) 사용자 정의 열 형식에 기본 키가 설정된 경우, 형식 구현은 바이너리 정렬을 지원해야 합니다.

외래 키 제약 조건 – 상세 개요

외래 키(FK)는 두 테이블 간의 연결을 설정하고, 외래 키 테이블에 저장될 데이터를 관리하는 데 사용되는 하나 이상의 열 조합입니다.

외래 키 참조는 두 테이블 간의 링크 생성 과정을 포함합니다. 즉, 한 테이블의 기본 키를 포함하는 열이 다른 테이블의 열에 의해 참조되는 경우입니다.

외래 키 참조 시나리오에서 한 테이블의 기본 키를 포함하는 열이 다른 테이블에서 참조될 때, 두 테이블 사이에 연관성이 형성됩니다.

실제 사용 사례로, `Sales.SalesOrderHeader` 테이블은 영업 사원과 판매 주문 간의 논리적 관계를 설정하기 위해 다른 테이블인 `Sales.Person` 테이블에 연결되는 외래 키를 가질 수 있습니다.

여기서 `SalesOrderHeader` 테이블의 `SalesPersonID` 열은 `SalesPerson` 테이블의 기본 키 열과 연결됩니다. `SalesPerson` 테이블 외래 키는 `SalesOrderHeader` 테이블의 `SalesPersonID` 열입니다.

이 관계는 규칙을 정의합니다. 즉, `SalesPersonID` 값은 `SalesPerson` 테이블에 존재하지 않으면 `SalesOrderHeader` 테이블에 존재할 수 없습니다.

테이블은 최대 253개의 다른 열과 테이블을 외래 키(또는 나가는 참조라고도 함)로 참조할 수 있습니다. 2016년부터 SQL 서버는 수신 참조라고도 하는 단일 테이블에서 참조할 수 있는 테이블 및 열의 수를 253개에서 10,000개로 늘렸습니다. 그러나 이러한 증가는 몇 가지 제한 사항을 동반합니다.

253개를 초과하는 외래 키 참조는 DELETE DML 작업에만 적용될 수 있으며, MERGE 및 UPDATE 작업은 지원되지 않습니다.

자신을 참조하는 외래 키를 가진 테이블은 최대 253개의 외래 키 참조를 가질 수 있습니다.

열 저장소 인덱스, 메모리 최적화 테이블, 그리고 분할된 외래 키 테이블의 경우, 외래 키 참조는 253개로 제한됩니다.

외래 키의 장점

앞서 언급했듯이, 외래 키 제약 조건은 관계형 데이터베이스의 완전성과 데이터 일관성을 유지하는 데 중요한 역할을 합니다. 외래 키 제약 조건이 필수적인 이유를 자세히 살펴보겠습니다.

참조 무결성: 외래 키 제약 조건은 모든 하위 테이블 레코드가 상위 테이블 레코드에 상응하도록 보장하여 두 테이블 간의 데이터 일관성을 유지합니다.

고아 레코드 방지: 상위 테이블이 삭제될 경우, 외래 키 제약 조건은 관련된 하위 테이블도 함께 삭제되도록 하여 데이터 불일치를 유발할 수 있는 고아 레코드 생성을 방지합니다.

성능 향상: 외래 키 제약 조건은 데이터베이스 관리 시스템이 테이블 관계를 기반으로 쿼리를 최적화할 수 있도록 하여 쿼리 성능을 향상시킵니다.

외래 키 제약 조건 인덱스

외래 키 제약 조건은 기본 키와 달리 자동으로 인덱스를 생성하지 않습니다. 외래 키 제약 조건에 대한 인덱스를 수동으로 생성하는 것은 다음과 같은 이유로 유익할 수 있습니다.

외래 키 열은 제약 조건과 연결된 열을 일치시켜 쿼리에서 관련 테이블의 데이터를 결합할 때 조인 기준으로 자주 사용됩니다. 인덱스는 데이터베이스가 외부 테이블에서 관련 데이터를 더 빨리 찾는 데 도움이 됩니다.
기본 키 제약 조건에 변경이 발생하면 관련 테이블의 외래 제약 조건과 함께 확인됩니다.

인덱스 생성은 필수가 아닙니다. 기본 키 및 외래 키 제약 조건 없이도 두 테이블의 데이터를 결합할 수 있습니다. 그러나 외래 키 제약 조건을 추가하면 테이블이 최적화되고, 키 사용 기준을 만족하는 쿼리에서 더 효율적으로 결합됩니다. 기본 키 제약 조건에 변경이 발생하면 관련 외래 제약 조건과 함께 확인됩니다.

SQL에서 외래 키 제약 조건 생성 팁

이제까지 왜 외래 키 제약 조건이 필요한지에 대해 알아봤다면, 이제는 실제로 외래 키 제약 조건을 생성하는 방법에 대해 집중해 보도록 하겠습니다.

테이블의 '외래 키' 필드는 다른 테이블의 '기본 키'를 참조합니다. 기본 키를 가진 테이블을 상위 테이블, 외래 키를 가진 테이블을 하위 테이블이라고 합니다. 이제 본격적으로 시작해 보겠습니다.

테이블 생성 시 외래 키 생성

테이블을 만들 때 참조 무결성을 유지하기 위해 외래 키 제약 조건을 동시에 생성할 수 있습니다. 방법은 다음과 같습니다.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

위 코드는 기본 정수 키 `order_id`, 정수 값 `customer_id`, 그리고 날짜 값 `order_date`를 포함하는 `orders`라는 테이블을 생성합니다. 이 예시에서 `FOREIGN KEY` 제약 조건은 `customer_id` 열에 추가되어 `customers` 테이블의 `customer_id` 열을 참조합니다.

테이블 생성 후 외래 키 생성

만약 이미 테이블을 생성했고, 이후에 외래 키 제약 조건을 추가하고 싶다면, `ALTER TABLE` 구문을 사용하면 됩니다. 아래 코드 스니펫을 확인해 보십시오.

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

위 코드는 `customers` 테이블의 `customer_id` 열을 참조하기 위해 `orders` 테이블의 `customer_id` 열에 외래 키 제약 조건을 추가하는 예시입니다.

기존 데이터 검증 없이 외래 키 생성

외래 키 제약 조건을 테이블에 추가하면 데이터베이스는 자동으로 기존 데이터를 검증하여 제약 조건과의 일관성을 확인합니다. 만약 데이터가 이미 일관성이 있으며 별도의 검증 없이 제약 조건을 추가하고 싶다면, 아래 방법을 따르세요.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
NOT VALIDATE;

위 코드에서 `NOT VALIDATE` 명령어는 데이터베이스에 기존 데이터 검증을 생략하도록 지시합니다. 이 방법은 대량의 데이터가 이미 존재하고 검증 과정을 생략하고자 할 때 유용합니다.

DELETE/UPDATE 동작을 통한 외래 키 생성

외래 키 제약 조건을 설정할 때, 참조되는 행이 업데이트되거나 삭제될 때 어떤 동작을 할지 지정할 수 있습니다. 이를 위해 캐스케이드 참조 무결성 제약 조건을 사용하여 처리할 동작을 지정합니다. 여기에는 다음 옵션들이 있습니다.

#1. 동작 없음(NO ACTION)

대부분의 데이터베이스와 마찬가지로, `NO ACTION` 규칙은 외래 키 제약 조건 생성 시 기본 동작으로 설정됩니다. 이는 참조된 행이 삭제되거나 업데이트될 때 아무런 동작도 취하지 않음을 의미합니다.

만약 외래 키 제약 조건을 위반하는 상황이 발생하면 데이터베이스 엔진에서 오류를 발생시킵니다. 하지만 이는 외래 키 제약 조건을 강제하는 것이기 때문에 참조 무결성 문제를 일으킬 수 있어 권장되지는 않습니다. 아래는 `NO ACTION` 규칙을 적용하는 방법의 예시입니다.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

#2. 연쇄 동작(CASCADE)

`CASCADE` 규칙은 외래 키 제약 조건 생성 시 `ON DELETE` 및 `ON UPDATE` 작업에 대한 또 다른 옵션입니다. 설정 시, 상위 테이블에서 행이 업데이트되거나 삭제될 때마다 참조된 행도 함께 업데이트되거나 삭제됩니다. 이 기술은 참조 무결성을 유지하는 데 매우 효과적입니다. 아래는 `CASCADE` 규칙을 적용하는 예시입니다.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

이 규칙은 신중하게 사용하지 않으면 예상치 못한 결과를 초래할 수 있으므로 주의가 필요합니다. 실수로 너무 많은 데이터를 삭제하거나 순환 참조를 만들지 않도록 주의해야 합니다. 따라서 이 옵션은 필요한 경우에만 신중하게 사용해야 합니다.

`CASCADE` 사용 시 지켜야 할 몇 가지 규칙은 다음과 같습니다.

타임스탬프 열이 외래 키 또는 참조 키의 일부인 경우 `CASCADE`를 지정할 수 없습니다.
테이블에 `INSTEAD OF DELETE` 트리거가 있는 경우, `ON DELETE CASCADE`를 지정할 수 없습니다.
테이블에 `INSTEAD OF UPDATE` 트리거가 있는 경우, `ON UPDATE CASCADE`를 지정할 수 없습니다.

#3. NULL 설정(SET NULL)

부모 테이블에서 해당 행을 삭제하거나 업데이트하면 외래 키를 구성하는 모든 값이 NULL로 설정됩니다. 이 제약 조건 규칙은 외래 키 열이 NULL 값을 허용해야 하며, `INSTEAD OF UPDATE` 트리거가 있는 테이블에는 지정할 수 없습니다. 아래는 `SET NULL` 규칙을 적용하는 방법의 예시입니다.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
ON UPDATE SET NULL

위 예시에서 `customers` 테이블의 해당 행이 삭제되거나 업데이트되면 `orders` 테이블의 외래 키 열인 `customer_id`가 NULL 값으로 설정됩니다.

#4. 기본값 설정(SET DEFAULT)

이 옵션은 부모 테이블에서 참조되는 행이 업데이트되거나 삭제되는 경우, 외래 키 열을 기본값으로 설정하는 데 사용됩니다.

모든 외래 키 열에 기본값이 설정되어 있는 경우 이 제약 조건이 실행됩니다. 만약 열이 NULL을 허용하는 경우, 기본값은 NULL로 설정됩니다. `INSTEAD OF UPDATE` 트리거가 있는 테이블에는 이 옵션을 지정할 수 없습니다. 아래는 `SET DEFAULT` 규칙을 적용하는 방법의 예시입니다.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT;

위 예시에서는 `orders` 테이블의 `customer_id` 열이 기본값으로 설정되며, 이는 `customers` 테이블에서 해당 행이 삭제되거나 업데이트될 때 발생합니다.

마지막으로

이 가이드에서는 기본 키 제약 조건을 간략히 복습하고, 외래 키 제약 조건에 대해 자세히 알아보았습니다. 또한 외래 키 제약 조건을 생성하는 여러 가지 기술에 대해서도 살펴보았습니다. 외래 키 제약 조건을 생성하는 다양한 방법이 있지만, 이 글에서는 그 모든 것을 다루었습니다.

새로운 기술들을 이해하셨기를 바랍니다. 또한 이러한 기술을 결합하여 사용할 수도 있습니다. 예를 들어 `CASCADE`, `SET NULL`, `SET DEFAULT`, 그리고 `NO ACTION` 제약 조건 메서드들은 참조 관계를 갖는 테이블에서 함께 결합하여 사용할 수 있습니다.

만약 테이블에서 `NO ACTION`이 발생하면, 다른 제약 조건 규칙으로 돌아갑니다. 다른 경우에는 `DELETE` 작업이 이러한 규칙의 조합을 트리거할 수 있으며, `NO ACTION` 규칙은 마지막으로 실행됩니다.

다음으로, SQL 치트 시트를 확인해 보시기 바랍니다.