[DBMS]MariaDB 외래 키 제약 조건: 완벽 가이드 및 실전 팁

데이터베이스 관리에서 무결성은 매우 중요합니다. 이를 보장하기 위한 방법 중 하나가 바로 외래 키(Foreign Key) 제약 조건입니다. MariaDB에서도 외래 키 제약 조건을 통해 데이터의 일관성과 무결성을 유지할 수 있습니다. 이번 포스팅에서는 MariaDB 외래 키 제약 조건의 개념부터 설정 방법, 다양한 옵션, 실전 예제, 그리고 자주 발생하는 문제와 해결 방법까지 완벽하게 안내해 드리겠습니다.

≣ 목차

• 기본 개념🧩
• MariaDB에서 외래 키 제약 조건 설정하기🔧
• 외래 키 제약 조건 옵션⚙️
• 실전 예제💻
• 외래 키 제약 조건 관리🛠️
• 자주 발생하는 문제와 해결 방법🔍
• 핵심 내용👀

기본 개념🧩

외래 키 제약 조건의 정의

외래 키 제약 조건은 한 테이블의 필드가 다른 테이블의 기본 키(Primary Key)와 연결되도록 강제하는 제약 조건입니다. 이를 통해 두 테이블 간의 참조 무결성을 보장합니다.

외래 키의 역할과 필요성

외래 키는 데이터베이스에서 중요한 역할을 합니다. 두 테이블 간의 관계를 명확하게 정의하고, 데이터의 일관성을 유지하며, 잘못된 데이터 입력을 방지합니다.

MARIADB에서 외래 키 사용의 장점

• 데이터 무결성 보장: 외래 키는 관련 데이터가 항상 일관성을 유지하도록 합니다.
• 자동 데이터 삭제/업데이트: 관련 데이터를 자동으로 삭제하거나 업데이트할 수 있습니다.
• 데이터베이스 구조 명확화: 데이터베이스 설계 시 테이블 간의 관계를 명확하게 합니다.

MariaDB에서 외래 키 제약 조건 설정하기🔧

외래 키 제약 조건 구문 및 옵션 설명

외래 키 제약 조건을 설정하기 위해서는 다음과 같은 SQL 구문을 사용합니다.

CREATE TABLE `자식테이블` (
    `id` INT PRIMARY KEY,
    `부모_id` INT,
    FOREIGN KEY (`부모_id`) REFERENCES `부모테이블`(`id`)
);

기본 사용법 및 예제

예를 들어, orders 테이블과 customers 테이블이 있다고 가정해 봅시다. orders 테이블의 customer_id가 customers 테이블의 id와 외래 키 관계를 맺도록 설정할 수 있습니다.

CREATE TABLE `customers` (
    `id` INT PRIMARY KEY,
    `name` VARCHAR(100)
);

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

외래 키 생성 시 주의사항

• 외래 키가 참조하는 칼럼과 동일한 데이터 타입을 사용해야 합니다.
• 참조되는 테이블(부모 테이블)에 기본 키 또는 고유 키(UNIQUE KEY)가 설정되어 있어야 합니다.

외래 키 제약 조건 옵션⚙️

외래 키 제약 조건 옵션

외래 키 설정 시 사용할 수 있는 주요 옵션은 다음과 같습니다.

1. CASCADE

CASCADE 옵션은 부모 테이블의 레코드가 변경될 때 자식 테이블의 레코드를 자동으로 변경하는 기능을 제공합니다.

• ON DELETE CASCADE: 부모 테이블의 레코드가 삭제될 때, 해당 레코드를 참조하는 자식 테이블의 모든 레코드도 함께 삭제됩니다.
• ON UPDATE CASCADE: 부모 테이블의 기본 키가 업데이트될 때, 해당 키를 참조하는 자식 테이블의 외래 키 값도 함께 업데이트됩니다.

2. SET NULL

SET NULL 옵션은 부모 테이블의 레코드가 변경될 때 자식 테이블의 외래 키 값을 NULL로 설정하는 기능을 제공합니다.

• ON DELETE SET NULL: 부모 테이블의 레코드가 삭제될 때, 해당 레코드를 참조하는 자식 테이블의 외래 키 값이 NULL로 설정됩니다.
• ON UPDATE SET NULL: 부모 테이블의 기본 키가 업데이트될 때, 해당 키를 참조하는 자식 테이블의 외래 키 값이 NULL로 설정됩니다.

3. RESTRICT

RESTRICT 옵션은 부모 테이블의 레코드가 변경될 때 자식 테이블의 레코드가 참조하고 있으면 변경을 거부하는 기능을 제공합니다.

• ON DELETE RESTRICT: 부모 테이블의 레코드가 자식 테이블에서 참조되고 있으면 삭제를 거부합니다.
• ON UPDATE RESTRICT: 부모 테이블의 기본 키가 자식 테이블에서 참조되고 있으면 업데이트를 거부합니다.

3.NO ACTIOIN

NO ACTION 옵션은 부모 테이블의 레코드가 변경될 때 자식 테이블에 아무런 자동 변경을 수행하지 않습니다. 부모 테이블의 변경이 자식 테이블의 제약 조건을 위반하면 에러를 발생시킵니다.

• ON DELETE NO ACTION: 부모 테이블의 레코드가 삭제될 때 자식 테이블에서 참조되고 있으면 에러를 발생시키고 삭제를 수행하지 않습니다.
• ON UPDATE NO ACTION: 부모 테이블의 기본 키가 업데이트될 때 자식 테이블에서 참조되고 있으면 에러를 발생시키고 업데이트를 수행하지 않습니다.

각 옵션의 차이점 및 사용 사례

• CASCADE: 계층적 데이터 구조에서 유용합니다. 예를 들어, 고객을 삭제하면 해당 고객의 주문도 모두 삭제됩니다.
• SET NULL: 관계가 끊어졌음을 명확하게 표시하고자 할 때 사용합니다. 예를 들어, 고객을 삭제하면 해당 주문의 고객 ID를 NULL로 설정합니다.
• RESTRICT: 중요한 참조 데이터를 실수로 삭제하지 않도록 방지합니다.
• NO ACTION: 복잡한 트랜잭션 처리 시 유용합니다.

예제와 함께 이해하기

-- CASCADE 예제
CREATE TABLE `orders` (
    `id` INT PRIMARY KEY,
    `order_date` DATE,
    `customer_id` INT,
    FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- SET NULL 예제
CREATE TABLE `orders` (
    `id` INT PRIMARY KEY,
    `order_date` DATE,
    `customer_id` INT,
    FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL ON UPDATE SET NULL
);

실전 예제💻

간단한 테이블 생성 및 외래 키 설정 예제

CREATE TABLE `products` (
    `id` INT PRIMARY KEY,
    `name` VARCHAR(100)
);

CREATE TABLE `orders` (
    `id` INT PRIMARY KEY,
    `order_date` DATE,
    `product_id` INT,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
);

다양한 외래 키 옵션을 적용한 복잡한 예제

CREATE TABLE `customers` (
    `id` INT PRIMARY KEY,
    `name` VARCHAR(100)
);

CREATE TABLE `orders` (
    `id` INT PRIMARY KEY,
    `order_date` DATE,
    `customer_id` INT,
    FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
);

외래 키 설정 후 데이터 삽입, 삭제, 업데이트 시의 동작 확인

-- 고객 및 주문 데이터 삽입
INSERT INTO `customers` (`id`, `name`) VALUES (1, 'John Doe');
INSERT INTO `orders` (`id`, `order_date`, `customer_id`) VALUES (1, '2023-01-01', 1);

-- 고객 데이터 업데이트
UPDATE `customers` SET `id` = 2 WHERE `id` = 1;

-- 주문 데이터 확인
SELECT * FROM `orders`; -- customer_id가 2로 업데이트됨

-- 고객 데이터 삭제
DELETE FROM `customers` WHERE `id` = 2;

-- 주문 데이터 확인
SELECT * FROM `orders`; -- customer_id가 NULL로 설정됨

외래 키 제약 조건 관리🛠️

외래 키 제약 조건 수정 및 삭제 방법

-- 외래 키 제약 조건 삭제
ALTER TABLE `orders` DROP FOREIGN KEY `orders_ibfk_1`;

-- 외래 키 제약 조건 수정
ALTER TABLE `orders` ADD CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES customers(`id`) ON DELETE CASCADE;

외래 키 제약 조건 확인 및 디버깅 팁

-- 외래 키 제약 조건 확인
SHOW CREATE TABLE `orders`;

-- 외래 키 제약 조건 디버깅
-- 관련 오류 메시지 확인
-- 외래 키 설정을 잘못한 부분을 수정

성능 최적화를 위한 외래 키 관리 요령

• 외래 키가 자주 참조되는 칼럼에 인덱스를 추가하여 성능을 향상합니다.
• 대량의 데이터를 삽입할 때는 외래 키 제약 조건을 잠시 비활성화하는 것도 고려해 볼 수 있습니다.

🙌여기서 잠깐! 인덱스 추가, 확인해 보기!

MariaDB에서 인덱스를 추가하는 방법은 매우 간단합니다. 인덱스를 추가하는 기본 구문은 다음과 같습니다.

CREATE INDEX `인덱스명` ON `테이블명`(`컬럼명`);

예를 들어, orders 테이블에서 customer_id 칼럼에 인덱스를 추가해 보겠습니다.

CREATE INDEX `idx_customer_id` ON `orders`(`customer_id`);

인덱스가 잘 추가되었는지 확인하려면 다음 명령을 사용할 수 있습니다. 이 명령은 orders 테이블에 있는 모든 인덱스를 보여줍니다.

SHOW INDEX FROM `orders`;

자주 발생하는 문제와 해결 방법🔍

외래 키 관련 오류 메시지와 그 해결 방법

• Error 1452: Cannot add or update a child row: 부모 테이블에 참조되는 키가 존재하지 않음을 의미합니다. 부모 테이블에 해당 키를 먼저 추가합니다.
• Error 1451: Cannot delete or update a parent row: 자식 테이블에서 참조 중인 부모 테이블의 데이터를 삭제 또는 업데이트하려 할 때 발생합니다. 자식 테이블의 관련 데이터를 먼저 처리합니다.

외래 키 설정 시 흔히 저지르는 실수와 피하는 방법

• 데이터 타입 불일치: 외래 키와 참조하는 키의 데이터 타입이 일치해야 합니다.
• 존재하지 않는 키 참조: 부모 테이블에 존재하지 않는 키를 참조하려고 하지 않도록 주의합니다.
• 적절한 옵션 설정: 데이터의 특성과 사용 시나리오에 맞는 옵션을 설정해야 합니다.

핵심 내용👀