Ch08. 데이터 무결성 - 외래 키 제약 조건

참조 무결성이란, 두 테이블의 관계가 항상 유효하고 일관된 상태를 유지해야 한다는 원칙이다.

 

예를 들어, orders 테이블의 user_id 컬럼은 반드시 users 테이블에 실제로 존재하는 "user_id" 값만을 참조해야 한다. 만약 존재하지도 않는 유령 회원의 주문이 있다면, 이 관계는 깨진 것이다. 이러한 테이블 간의 관계 무결성을 강제하는 가장 강력한 제약 조건이 바로 외래 키(Foreign Key)다.

 

외래 키(FK)의 역할: 유령 데이터를 막아라

• 자식 테이블(orders)에 INSERT, UPDATE 할 때: 부모 테이블(users)에 존재하지 않는 "user_id" 값을 자식 테이블(orders)의 "user_id" 컬럼에 넣으려는 시도를 막는다. (유령 주문 생성 방지)
• 부모 테이블(users)에서 DELETE, UPDATE 할 때: 자식 테이블(orders)에서 참조하고 있는 "user_id" 값을 가진 행을 함부로 삭제하거나 변경하지 못하게 막는다. (기존 주문을 유령 주문으로 만드는 것 방지)

유령 주문 생성 시도

"존재하지 않는 user_id인 999번 고객의 주문을 넣어보자."

insert into orders(user_id, product_id, quantity)  values(999, 1, 1);

• Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_shop2`.`orders`, CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
• 데이터베이스는 "fk_orders_users" 외래 키 제약 조건이 실패했다는 명확한 에러를 출력하며, 이 INSERT를 거부했다.

 

주문이 있는 고객 삭제 시도

"'션' 고객(user_id =1)은 이미 주문 기록이 있다."

delete from users where user_id = 1;

• Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`my_shop2`.`orders`, CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
• 데이터베이스는 이 고객을 참조하는 주문이 있기 때문에 삭제할 수 없다는 오류를 발생시키며 삭제를 막는다
  • 만약 이 삭제 쿼리가 성공한다면 orders 테이블에는 "션" 고객(user_id =1)의 주문이 남아있지만, users 테이블의 "션"은 제거된다.
  • 결과적으로 주문 내역만 있고, 실제 고객은 사라지는 심각한 문제가 발생한다. 그리고 향후 이 주문이 누구의 주문인지 찾을 수 없게 된다.
• 만약 부모 테이블의 션(user_id  = 1) 데이터를 삭제하고 싶다면 다음과 같이 자식 테이블의 "션" 관련 데이터를 먼저 삭제하고, 이후에 부모 테이블을 삭제하는 순서로 진행해야 한다.

 

ON DELETE / ON UPDATE 옵션

데이터베이스가 부모 데이터의 삭제나 수정을 무조건 막는 것이 기본값(RESTRICT)이며 가장 안전한 정책이다. 하지만 때로는 비즈니스 규칙에 따라 다른 정책이 필요할 수 있다. 예를 들어 "회원이 탈퇴하면, 그 회원의 모든 주문 기록도 함께 삭제되어야 한다" 와 같은 경우다.

 

이럴 때 사용하는 것이 외래 키의 "ON DELETE"와 "ON UPDATE" 옵션이다.

• RESTRICT (기본값): 자식 테이블에 참조하는 행이 있으면 부모 테이블의 행을 삭제/수정할 수 없다. (방금 확인한 동작)
• CASCADE: 부모 테이블의 행이 삭제/수정되면, 그를 참조하는 자식 테이블의 행도 함께 자동으로 삭제/수정된다.
• SET NULL: 부모 테이블의 행이 삭제/수정되면, 자식 테이블의 해당 외래 키 컬럼의 값을 "NULL"
  • 단, 이 옵션을 쓰려면 자식 테이블의 외래 키 컬럼이 "NULL"을 허용해야 한다.

-- 실습을 위해 기존 테이블 삭제 후 CASCADE 옵션으로 재생성
DROP TABLE orders;
CREATE TABLE orders (
	order_id BIGINT AUTO_INCREMENT,
	user_id BIGINT NOT NULL,
	product_id BIGINT NOT NULL,
	order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	quantity INT NOT NULL,
	status VARCHAR(50) DEFAULT 'PENDING',
	PRIMARY KEY (order_id),
	CONSTRAINT fk_orders_users FOREIGN KEY (user_id)
	REFERENCES users(user_id) ON DELETE CASCADE, -- CASCADE 옵션 추가
	CONSTRAINT fk_orders_products FOREIGN KEY (product_id)
	REFERENCES products(product_id)
);
-- 션 회원 다시 등록
INSERT INTO users(user_id, name, email, address, birth_date) VALUES (1,'션', 'sean@example.com','서울시 강남구', '1990-01-15');
-- 주문 데이터 다시 입력
INSERT INTO orders(user_id, product_id, quantity, status) VALUES (1, 1, 1, 'COMPLETED'),(1, 4, 2, 'COMPLETED'),(2, 2, 1, 'SHIPPED');

 

회원 데이터("션")

select * from users where user_id = 1;

쿼리 결과

• "션" 고객이 생성되었다.

주문 데이터("션")

select * from orders where user_id = 1;

쿼리 결과

• "션"(user_id = 1)이 주문한 주문이 2건이 존재한다.(order_id = 4, 5)

"션" 고객 삭제

delete from users where user_id = 1;

• 아무 에러 없이 쿼리가 성공적으로 실행된다.
• "션"(user_id = 1)인 고객의 주문 내역이 모두 연쇄적으로 함께 삭제된 것을 확인할 수 있다(order_id = 4, 5  제거)

CASCADE  옵션은 매우 편리하지만, 의도치 않은 대량의 데이터 삭제를 유발할 수 있으므로 반드시 비즈니스 로직을 명확히 이해하고 신중에 신중을 기해서 사용해야 한다

CASCADE - 실무 이야기

CASCADE 옵션은 분명 편리한 기능이지만, 잘못 사용할 경우 예상치 못한 대량의 데이터가 함께 삭제되는 경우가 있다. 특히 관계가 복잡하게 얽혀 있는 경우에는 파급 효과를 예측하기 어렵다. 이런 문제로 실무에서는 CASCADE 옵션은 잘 사용하지 않는다. 대신에 애플리케이션 계층에서 명시적으로 관련된 데이터를 처리하는 방식이 더 선호된다.

 

외래 키는 테이블 간의 관계를 정의하는 것을 넘어, 그 관계가 항상 올바른 상태를 유지하도록 강제하는 데이터베이스의 핵심적인 무결성 장치다.