패스트캠퍼스 환급챌린지 59일차: 데이터엔지니어링 초격차 강의 후기

본 포스팅은 패스트캠퍼스 환급 챌린지 참여를 위해 작성하였습니다.

SQL 심화: 데이터 관계의 수호자 - Foreign Key 제약 조건과 참조 무결성 마스터하기 🔗

 

 

안녕하세요! 🌟

 

지난 시간에는 DDL(Data Definition Language)의 핵심 명령어 CREATE, ALTER, DROP을 통해 데이터베이스의 뼈대를 세우고 관리하는 방법을 배웠습니다. 멋진 건물(데이터베이스)을 짓기 위한 설계도를 그리고, 필요에 따라 수정하며, 때로는 철거하는 방법을 익힌 셈이죠! 🏗️

 

오늘은 이렇게 만들어진 여러 테이블들이 서로 어떻게 의미 있는 관계를 맺고, 그 관계를 어떻게 건강하게 유지하는지에 대해 깊이 파고들 시간입니다. 바로 Foreign Key(외래 키) 와 그에 따른 참조 동작(Referential Actions) 에 관한 이야기입니다! 테이블 간의 끈끈한 연결고리를 만들고, 데이터의 일관성과 정확성을 지키는 핵심 기술이니, 오늘도 집중해서 따라와 주세요! 준비되셨나요? 시작하겠습니다! 🚀

 

🤝 Foreign Key (외래 키)란 무엇일까요?

Foreign Key(FK)는 한 테이블의 컬럼(또는 컬럼들의 집합)이 다른 테이블의 기본 키(Primary Key, PK)를 참조하도록 하여 테이블 간의 관계를 정의하는 제약 조건입니다. 마치 한 쪽 테이블이 다른 쪽 테이블을 "가리키는" 것과 같아요.

이 관계를 통해 우리는 다음과 같은 중요한 이점을 얻을 수 있습니다:

1. 참조 무결성 (Referential Integrity): 자식 테이블(FK를 가진 테이블)의 FK 값은 반드시 부모 테이블(PK를 가진 테이블)에 존재하는 PK 값이거나, NULL이어야 합니다 (FK 컬럼이 NULL을 허용하는 경우). 이를 통해 존재하지 않는 데이터를 참조하는 "고아 레코드"가 생기는 것을 방지합니다.
2. 데이터 일관성 유지: 관계된 데이터들이 서로 모순 없이 일관된 상태를 유지하도록 돕습니다.
3. 논리적 데이터 모델링: 데이터베이스의 구조를 더 명확하고 논리적으로 표현할 수 있게 해줍니다.

📌 기본 문법 (테이블 생성 시 Foreign Key 정의):

CREATE TABLE 자식테이블명 (
    컬럼명1 데이터타입,
    ...
    참조컬럼명 데이터타입, -- 부모 테이블의 PK와 데이터 타입이 일치해야 함
    ...
    CONSTRAINT FK_제약조건이름 -- 선택 사항, 이름을 지정하면 관리가 용이
    FOREIGN KEY (참조컬럼명) REFERENCES 부모테이블명(부모테이블_PK컬럼명)
    [ON DELETE 참조액션]
    [ON UPDATE 참조액션]
);

✨ MySQL 예시 (departments와 employees 테이블 관계 설정):
먼저 부서 정보를 담을 departments 테이블(부모 테이블)을 만들어 볼게요.

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);

이제 직원 정보를 담을 employees 테이블(자식 테이블)을 만들고, department_id를 Foreign Key로 설정하여 departments 테이블을 참조하도록 하겠습니다.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT, -- departments 테이블의 department_id를 참조
    hire_date DATE,
    CONSTRAINT fk_emp_dept -- FK 제약 조건 이름 지정
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
    -- 아직 참조 액션은 지정하지 않았습니다. 아래에서 자세히 다룰게요!
);

 

이렇게 하면 employees 테이블의 department_id에는 departments 테이블에 실제로 존재하는 department_id 값만 입력되거나, department_id 컬럼이 NULL을 허용한다면 NULL 값이 들어갈 수 있습니다.

 

🔄 MySQL Referential Actions: 관계의 변화에 대응하기

자, 이제 핵심입니다! 부모 테이블의 데이터가 변경(UPDATE)되거나 삭제(DELETE)될 때, 이를 참조하고 있는 자식 테이블의 데이터는 어떻게 처리되어야 할까요? 이때 사용되는 것이 바로 Referential Actions (참조 동작) 입니다. MySQL (InnoDB 스토리지 엔진 기준)에서 주로 사용되는 참조 동작들은 다음과 같습니다:

1. NO ACTION
2. RESTRICT
3. SET NULL
4. CASCADE

이들은 ON DELETE [참조액션] 또는 ON UPDATE [참조액션] 형태로 Foreign Key 정의 시 함께 지정됩니다.

 

🚫 1. ON DELETE NO ACTION / ON DELETE RESTRICT

• NO ACTION: (MySQL InnoDB에서는 RESTRICT와 동일하게 동작) 부모 테이블의 행을 삭제하거나 PK 값을 수정하려고 할 때, 해당 값을 참조하는 자식 테이블의 행이 존재하면 작업을 거부하고 오류를 발생시킵니다. 즉, "자식이 있으면 부모는 떠날 수 없다!"는 규칙입니다.
• RESTRICT: NO ACTION과 거의 동일하게 동작합니다. 표준 SQL에서는 검사 시점에 차이가 있을 수 있지만, MySQL InnoDB에서는 사실상 동일하게 자식 레코드가 존재하면 부모 레코드의 변경/삭제를 막습니다.

✨ 예시 (NO ACTION / RESTRICT):
만약 employees 테이블의 Foreign Key가 ON DELETE RESTRICT (또는 NO ACTION)로 설정되어 있고, 'Sales' 부서(예: department_id = 1)에 소속된 직원이 있다면, departments 테이블에서 'Sales' 부서 정보를 삭제하려고 시도하면 오류가 발생하며 삭제가 거부됩니다.

-- departments 테이블에 데이터 삽입
INSERT INTO departments (department_name) VALUES ('Engineering'), ('Sales');

-- employees 테이블에 데이터 삽입 (Sales 부서 참조)
INSERT INTO employees (first_name, last_name, department_id, hire_date)
VALUES ('John', 'Doe', 2, '2023-01-15'); -- Sales 부서의 department_id가 2라고 가정

-- Sales 부서 삭제 시도 (department_id = 2)
-- DELETE FROM departments WHERE department_id = 2;
-- -> ERROR! (Cannot delete or update a parent row: a foreign key constraint fails)

 

이것이 기본 동작이며, 데이터의 안전을 우선시하는 설정입니다.

 

🗑️ 2. ON DELETE CASCADE

• 부모 테이블의 행이 삭제되면, 해당 행을 참조하고 있던 자식 테이블의 모든 행도 함께 자동으로 삭제됩니다. "부모가 떠나면 자식도 함께!" 라는 강력한 규칙입니다.
• ON UPDATE CASCADE도 유사하게 동작합니다. 부모 테이블의 PK 값이 변경되면, 자식 테이블의 FK 값도 자동으로 새로운 값으로 변경됩니다.

✨ 예시 (ON DELETE CASCADE / ON UPDATE CASCADE):
employees 테이블의 Foreign Key를 다음과 같이 CASCADE 옵션으로 정의했다고 가정해 봅시다.

ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept; -- 기존 FK 제약 삭제
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept_cascade
FOREIGN KEY (department_id) REFERENCES departments(department_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

 

이제 departments 테이블에서 'Sales' 부서 (department_id = 2)를 삭제하면, 'Sales' 부서에 소속된 모든 직원 정보도 employees 테이블에서 자동으로 삭제됩니다.

-- Sales 부서 (department_id = 2) 삭제
DELETE FROM departments WHERE department_id = 2;
-- -> employees 테이블에서 department_id가 2였던 모든 직원 정보도 함께 삭제됨!

 

만약 departments 테이블에서 department_id를 변경할 수 있다면 (AUTO_INCREMENT가 아닌 경우 또는 다른 PK로 변경 시), ON UPDATE CASCADE에 의해 employees 테이블의 department_id도 자동으로 변경됩니다.

-- Sales 부서의 department_id를 2에서 10으로 변경 (PK 변경이 가능하다면)
-- UPDATE departments SET department_id = 10 WHERE department_id = 2;
-- -> employees 테이블에서 department_id가 2였던 모든 직원들의 department_id도 10으로 자동 변경됨!

 

⚠️ 주의: CASCADE 옵션은 매우 편리하지만, 의도치 않은 대량 데이터 삭제/변경을 유발할 수 있으므로 신중하게 사용해야 합니다.

🤷 3. ON DELETE SET NULL

• 부모 테이블의 행이 삭제되면, 해당 행을 참조하던 자식 테이블의 FK 컬럼 값이 NULL로 설정됩니다. "부모가 떠나면 자식은 소속을 잃는다 (하지만 존재는 한다)"는 규칙입니다.
• 이 옵션을 사용하려면 자식 테이블의 FK 컬럼이 NULL 값을 허용하도록 (NULL 또는 DEFAULT NULL) 정의되어 있어야 합니다.
• ON UPDATE SET NULL도 유사하게 동작합니다. 부모 테이블의 PK 값이 변경되면, 자식 테이블의 FK 값은 NULL로 설정됩니다.

✨ 예시 (ON DELETE SET NULL):
employees 테이블의 Foreign Key를 다음과 같이 SET NULL 옵션으로 정의하고, department_id 컬럼이 NULL을 허용한다고 가정해 봅시다.

-- employees 테이블의 department_id가 NULL을 허용하는지 확인/수정
-- ALTER TABLE employees MODIFY COLUMN department_id INT NULL;

ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept_cascade; -- 이전 FK 제약 삭제
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept_set_null
FOREIGN KEY (department_id) REFERENCES departments(department_id)
ON DELETE SET NULL
ON UPDATE SET NULL; -- ON UPDATE도 SET NULL로 설정 가능

 

이제 departments 테이블에서 'Sales' 부서 (department_id = 2)를 삭제하면, 'Sales' 부서에 소속되었던 직원들의 department_id 값은 employees 테이블에서 NULL로 변경됩니다. 직원 정보 자체는 삭제되지 않습니다.

-- Sales 부서 (department_id = 2) 삭제
DELETE FROM departments WHERE department_id = 2;
-- -> employees 테이블에서 department_id가 2였던 모든 직원들의 department_id가 NULL로 변경됨!

 

🎯 어떤 참조 액션을 선택해야 할까요?

참조 액션의 선택은 데이터의 성격과 비즈니스 로직에 따라 달라집니다.

• RESTRICT (또는 NO ACTION): 가장 기본적이고 안전한 옵션입니다. 자식 데이터가 존재하는 한 부모 데이터를 함부로 삭제/변경할 수 없도록 하여 데이터 무결성을 강하게 유지합니다. 대부분의 경우 이 옵션으로 시작하는 것이 좋습니다.
• CASCADE: 부모와 자식 데이터가 매우 밀접하게 연관되어 있어, 부모가 사라지면 자식도 의미가 없어지는 경우에 사용합니다. (예: 게시글과 댓글 - 게시글이 삭제되면 댓글도 함께 삭제) 편리하지만, 데이터 유실의 위험이 있으므로 반드시 필요한 경우에만 신중하게 사용해야 합니다.
• SET NULL: 부모 데이터가 삭제되더라도 자식 데이터는 독립적으로 존재할 수 있지만, 더 이상 특정 부모와 연결되지 않음을 나타내고 싶을 때 사용합니다. (예: 직원이 특정 부서에 속해 있다가 부서가 해체되면, 직원은 존재하지만 '소속 없음' 상태가 됨) FK 컬럼이 NULL을 허용해야 합니다.

🚨 Foreign Key 및 참조 액션 사용 시 주의사항!

• 데이터 타입 일치: FK 컬럼과 참조하는 PK 컬럼은 반드시 데이터 타입이 일치해야 합니다.
• 인덱싱: FK 컬럼에는 자동으로 인덱스가 생성되지 않는 경우가 있습니다 (MySQL InnoDB는 자동으로 생성해 줌). 관계된 테이블 간의 JOIN 성능이나 참조 무결성 검사 속도를 높이기 위해 FK 컬럼에 수동으로 인덱스를 생성하는 것을 고려하세요.
• 성능: CASCADE나 SET NULL 같은 액션은 연쇄적인 데이터 변경을 유발하므로, 대량의 데이터에 적용될 경우 성능에 영향을 줄 수 있습니다. 트랜잭션이 길어지거나 잠금이 오래 유지될 수 있습니다.
• 복잡성: 여러 테이블에 걸쳐 복잡한 CASCADE 관계가 설정되어 있으면, 특정 데이터 변경이 어떤 파급 효과를 가져올지 예측하기 어려울 수 있습니다.
• 테스트는 필수: 참조 액션을 설정한 후에는 반드시 다양한 시나리오(삭제, 수정 등)를 테스트하여 의도한 대로 동작하는지, 예상치 못한 부작용은 없는지 꼼꼼히 확인해야 합니다.

🏁 정리하며: 관계형 데이터베이스의 핵심, Foreign Key!

오늘은 테이블 간의 관계를 정의하고 데이터의 무결성을 지키는 데 핵심적인 역할을 하는 Foreign Key와, 그 관계가 변동될 때 어떻게 대응할지를 결정하는 MySQL의 Referential Actions (NO ACTION, RESTRICT, SET NULL, CASCADE)에 대해 자세히 알아보았습니다.

• Foreign Key: 테이블 간의 연결고리, 데이터의 정합성 보장.
• NO ACTION/RESTRICT: 자식이 있으면 부모 변경/삭제 불가 (기본적이고 안전).
• CASCADE: 부모 변경/삭제 시 자식도 함께 변경/삭제 (강력하지만 신중히).
• SET NULL: 부모 변경/삭제 시 자식의 참조값을 NULL로 (FK 컬럼 NULL 허용 필수).

이러한 제약 조건과 참조 액션들을 잘 이해하고 활용한다면, 여러분의 데이터베이스는 더욱 견고하고 신뢰성 높은 시스템으로 거듭날 수 있을 거예요. 데이터 간의 관계를 설계하고 정의하는 것은 데이터베이스 관리의 매우 중요한 부분입니다.

 

오늘도 데이터베이스의 깊은 곳까지 탐험하시느라 정말 수고 많으셨습니다! 👍 이 지식들이 여러분의 데이터를 더욱 안전하고 체계적으로 관리하는 데 큰 도움이 되길 바랍니다!

 

그럼 다음 시간에는 또 다른 SQL의 흥미로운 주제로 여러분을 찾아뵙겠습니다! 👋

https://abit.ly/lisbva