Foreign Key 제대로 사용하기 2

📌 Foreign Key로 보장되는 참조무결성

• review 테이블 : 자식 테이블
• course 테이블 : 부모 테이블

✅ review 테이블에 새로운 row 추가

INSERT INTO review (course_id, star, comment)
	VALUES (10, 5, '정말 좋은 수업이에요!');

course_id 컬럼에 10이라는 값이 들어가게 된다. 이 값은 들어가면 안 되는 값이다. 10은 course 테이블의 id 컬럼에 존재하지 않는 값이기 때문이다.

INSERT INTO review (course_id, star, comment)
	VALUES (8, 5, '정말 좋은 수업이에요!');

위처럼 작성하면 row가 추가된다.

---

📌 부모 테이블 row가 삭제될 때 RESTRICT 정책

부모 테이블의 row가 삭제된다면, 그 row를 참조하던 자식 테이블의 row는 어떻게 될까 ❓

사용자가 일정의 정책을 통해 정할 수 있다.

RESTRICT 정책이 설정되어 있으면 자신을 참조하고 있는 자식 테이블의 row가 하나라도 있는 부모 테이블의 row는 삭제할 수 없다. 삭제하려면, row를 참조하고 있는 자식 테이블의 row를 모두 지워야만 가능하다.

✅ 객체 지향 프로그래밍 수업을 삭제

DELETE FROM course WHERE id = 5;

Cannot delete or update a parent row: a foreign key constraint fails (course_rating.review, CONSTRAINT fk_review_table FOREIGN KEY (course_id) REFERENCES course (id) ON DELETE RESTRICT ON UPDATE RESTRICT) 0.016 sec

위와 같은 오류가 발생한다.

---

📌 부모 테이블의 row가 삭제될 때 - CASCADE 정책

CASCADE는 폭포수처럼 떨어지다, 연쇄 작용을 일으키다 라는 뜻을 갖고 있다.

부모 테이블의 row가 삭제될 때 참조하고 있던 자식 테이블의 row들도 함께 삭제된다.

ALTER TABLE `course_rating`.`review` 
DROP FOREIGN KEY `fk_review_table`;
ALTER TABLE `course_rating`.`review` 
ADD CONSTRAINT `fk_review_table`
  FOREIGN KEY (`course_id`)
  REFERENCES `course_rating`.`course` (`id`)
  ON DELETE CASCADE
  ON UPDATE RESTRICT;

✅ 객체 지향 프로그래밍 row 삭제

DELETE FROM course WHERE id = 5;

---

📌 부모 테이블의 row가 삭제될 때 - SET NULL 정책

SET NULL 정책은 부모 테이블의 row가 삭제되었을 때 그것을 참조하는 자식 테이블의 Foreign Key 컬럼의 값을 NULL로 바꾼다.

ALTER TABLE `course_rating`.`review` 
DROP FOREIGN KEY `fk_review_table`;
ALTER TABLE `course_rating`.`review` 
ADD CONSTRAINT `fk_review_table`
  FOREIGN KEY (`course_id`)
  REFERENCES `course_rating`.`course` (`id`)
  ON DELETE SET NULL
  ON UPDATE RESTRICT;

✅ 자료구조와 알고리즘 수업을 삭제

DELETE FROM course WHERE id = 2;

---

📌 부모 테이블의 row에서 참조 당하는 컬럼이 갱신될 때

row가 삭제될 때 뿐만 아니라 부모 테이블의 row에서 참조 당하는 컬럼의 값이 갱신될 때도 세 가지 정책이 존재한다.

1. RESTRICT

✅ id가 1인 프로그래밍 파이썬 기초 수업을 100으로 갱신

UPDATE course SET id = 100 WHERE id = 1;

UPDATE course SET id = 100 WHERE id = 1 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (course_rating.review, CONSTRAINT fk_review_table FOREIGN KEY (course_id) REFERENCES course (id) ON DELETE SET NULL ON UPDATE RESTRICT) 0.016 sec

위와 같은 오류가 발생한다.

2. CASCADE

ALTER TABLE `course_rating`.`review` 
DROP FOREIGN KEY `fk_review_table`;
ALTER TABLE `course_rating`.`review` 
ADD CONSTRAINT `fk_review_table`
  FOREIGN KEY (`course_id`)
  REFERENCES `course_rating`.`course` (`id`)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

UPDATE course SET id = 100 WHERE id = 1;

부모 테이블과 자식 테이블 모두 100으로 변경되었다.

3. SET NULL

ALTER TABLE `course_rating`.`review` 
DROP FOREIGN KEY `fk_review_table`;
ALTER TABLE `course_rating`.`review` 
ADD CONSTRAINT `fk_review_table`
  FOREIGN KEY (`course_id`)
  REFERENCES `course_rating`.`course` (`id`)
  ON DELETE SET NULL
  ON UPDATE SET NULL;

UPDATE course SET id = 200 WHERE id = 100;

---