외래 키를 고려한 데이터 삭제와 수정

자, 이제 우리 외래 키(FOREIGN KEY)가 뭔지, 어떻게 동작하는지, 그리고 그걸로 테이블 만드는 것도 연습해봤지. 근데 이제 데이터를 삭제하거나 연결된 레코드를 수정해야 할 때는 어떻게 해야 할까? 지금부터 외래 키랑 같이 데이터 삭제/수정이 어떻게 돌아가는지, 그리고 여기서 쓸 수 있는 꿀팁들 알려줄게.

데이터베이스를 복잡한 카드탑이라고 생각해봐. 카드 한 장을 빼면 전체 구조가 무너질 수도 있잖아? 이럴 때 외래 키가 등장해서, 연결된 데이터를 지울 때 우리 데이터베이스가 "무너지는" 걸 막아줘. 이게 어떻게 동작하는지 같이 보자.

데이터를 삭제하려고 하면 어떻게 될까?

테이블에 외래 키가 있으면, DBMS가 그 레코드가 다른 테이블이랑 연결되어 있는지 체크해. 만약 연결되어 있으면, 삭제 시도할 때 데이터 무결성 오류가 날 수 있어. 이런 깜짝 놀랄 일 없게, 외래 키에 미리 동작을 지정할 수 있어. 이 동작들은 ON DELETE 옵션으로 설정해.

ON DELETE로 동작 설정하기

외래 키 만들 때 아래 규칙 중 하나를 지정할 수 있어:

1. ON DELETE CASCADE: 부모 테이블에서 레코드를 삭제하면, 연결된 자식 테이블 레코드도 자동으로 다 삭제돼.
2. ON DELETE SET NULL: 연결된 레코드를 삭제하는 대신, 외래 키 값을 NULL로 바꿔줘.
3. ON DELETE SET DEFAULT: 외래 키 값을 기본값으로 바꿔줘.
4. ON DELETE RESTRICT (기본 동작): 연결된 레코드가 있으면 삭제 불가, 오류가 나와.
5. ON DELETE NO ACTION: 거의 RESTRICT랑 비슷한데, 무결성 체크가 트랜잭션 끝날 때까지 미뤄져.

예시: cascade 삭제 ON DELETE CASCADE

-- 고객 테이블
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 주문 테이블
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- 데이터 삽입
INSERT INTO customers (name) VALUES ('이반 이바노프');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- 고객 삭제
DELETE FROM customers WHERE customer_id = 1;

-- orders 테이블에서 무슨 일이 일어났는지 확인
SELECT * FROM orders; -- 아무 레코드도 없어, cascade로 다 삭제됐어!

customers 테이블에서 레코드를 삭제하면, PostgreSQL이 자동으로 그 고객의 모든 주문을 orders 테이블에서 삭제해줘.

예시: NULL로 연결 끊기 ON DELETE SET NULL

-- 새로운 동작 규칙이 적용된 주문 테이블
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- 데이터 삽입
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- 고객 삭제
DELETE FROM customers WHERE customer_id = 1;

-- orders_with_null 테이블 확인
SELECT * FROM orders_with_null;

결과:

ON DELETE SET NULL을 쓰면 주문은 남기고, 이제 없는 고객이랑 연결만 끊을 수 있어.

외래 키를 고려한 데이터 수정

삭제뿐만 아니라, 부모 테이블에서 데이터가 바뀌면 연결된 레코드에도 영향이 갈 수 있어. 예를 들어, 고객의 customer_id가 바뀌면 어떻게 될까? 여기서 ON UPDATE 옵션이 등장해.

ON UPDATE로 동작 설정하기

부모 테이블에서 값이 바뀔 때 아래 전략 중 하나로 처리할 수 있어:

1. ON UPDATE CASCADE: 부모 테이블에서 외래 키 값이 바뀌면, 연결된 모든 레코드도 자동으로 업데이트돼.
2. ON UPDATE SET NULL: 자식 테이블의 외래 키 값을 NULL로 바꿔줘.
3. ON UPDATE SET DEFAULT: 기본값으로 바꿔줘.
4. ON UPDATE RESTRICT: 연결된 레코드가 있으면 외래 키 값 변경 불가.
5. ON UPDATE NO ACTION: 무결성 체크가 트랜잭션 끝날 때까지 미뤄져.

예시: cascade 업데이트 ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- 데이터 삽입
INSERT INTO customers_with_cascade (name) VALUES ('이반 이바노프');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- customer_id 변경
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- orders_with_cascade 테이블 확인
SELECT * FROM orders_with_cascade;

결과:

customer_id가 바뀌면 PostgreSQL이 orders_with_cascade 테이블에서도 자동으로 업데이트해줘.

실습: enrollments 테이블

우리 학생 students랑 강좌 courses 예시 기억나지? 이번엔 수강신청 테이블 enrollments로 데이터 삭제 관리하는 걸 해볼 거야.

-- 학생 테이블
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 강좌 테이블
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- 중간 테이블(수강신청)
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- 데이터 삽입
INSERT INTO students (name) VALUES ('알렉세이 페트로프');
INSERT INTO courses (title) VALUES ('수학');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- 학생 삭제
DELETE FROM students WHERE student_id = 1;

-- enrollments 테이블 확인
SELECT * FROM enrollments; -- 비었어! 레코드가 자동으로 삭제됐어.

흔한 실수와 예방법

자주 하는 실수 중 하나는 부모 테이블에서 레코드를 삭제하려고 하면서 외래 키 동작을 제대로 안 맞춘 거야. 예를 들어, ON DELETE에 아무것도 안 쓰면 기본적으로 RESTRICT가 적용돼서 오류가 나와.

그리고 cascade(CASCADE)를 너무 자주 쓰면, 의도치 않게 많은 데이터를 날릴 수도 있으니까 조심해야 해.

이런 문제를 피하려면 아래 팁을 참고해:

• 항상 ON DELETE랑 ON UPDATE 동작을 앱 로직에 맞게 신중하게 정해.
• 중요한 작업 전에는 꼭 확인 쿼리를 추가해서 뭘 지우거나 바꾸는지 체크해.
• 에러 나면 롤백할 수 있게 트랜잭션을 활용해.