ORACLE DB - ON UPDATE CASCADE (TRIGGER 사용해서 UPDATE 하기)

ON UPDATE CASCADE는 참조하고 있는 다른 테이블의 컬럼(FK)들도 같이 UPDATE 하겠다는 제약 조건이다.

 

ON UPDATE CASCADE는 아래와 같은 예시에서 사용된다.

 

먼저 아래와 같이 두 개의 테이블이 있다고 가정해보자.

 

COURSE

SECTION

 

COURSE 테이블은 과목명과 과목 번호를 가지고 있고,

SECTION 테이블은 어떠한 과목이 어떤 학기에 열리는지를 담고 있다.

 

이때 두 테이블의 PK, FK, 관계를 살펴보면

COURSE 테이블의 PK는 Course_number이고

SECTION 테이블의 Course_number는 COURSE 테이블의 Course_number의 FK이다.

 

즉 SECTION 테이블의 Course_number는 COURSE 테이블의 Course_number를 참조하고 있다.

 

따라서 만약 COURSE의 Course_number를 바꾼다면 SECTION의 Course_number도 바뀌어야 한다.

 

예제로 살펴보자.

 

Q) 'Database'의 Course_number를 SW0002로 바꿔주세요!

 

SQL문은 어렵지 않을 것이다.

UPDATE COURSE SET Course_number = 'SW0002' WHERE Course_name = 'Database';

 

근데 실행해보면?

 

 

Error가 발생한다!

child record found, 즉 자식 테이블, 참조하고 있는 테이블이 있다는 것이다.

 

이러한 경우에 SQL에서는 다음과 같이 ON UPDATE CASCADE를 사용한다.

FOREIGN KEY (COURSE_NUMBER) REFERENCES COURSE(COURSE_NUMBER) ON UPDATE CASCADE

Constraint를 주어 제약조건의 이름도 줄 수 있지만 어쨌든 형식은 위와 같다.

 

이렇게 FK를 생성하면 만약 PK가 업데이트(변경)시 FK도 같이 변경이 된다.

 

B. U. T

 

아마 ORACLE DB를 사용하는 사람들은 해당 구문이 실행되지 않을 것이다. (궁금하면 직접 해보시길)

 

왜?

 

그야 ORACLE에서 ON UPDATE CASCADE를 지원하지 않거든요

 

그럼 어떻게 해야 하는가

 

여기서 TRIGGER 가 사용된다.

 

TRIGGER에 대한 자세한 내용은 여기서 소개하지 않겠다.

간단하게 설명하면 말 그대로 TRIGGER, 간단한 규칙 같은 것이다.

 

TRIGGER는 다음과 같이 사용된다.

 

- AFTER, BEFORE를 통해 TRIGGER가 언제 실행될지 결정

- FOR EACH ROW, 즉 모든 테이블을 반복하면서 실행된다

- BEGIN과 END 사이에 SQL문 삽입 가능, 이때 반드시 ; 로 끝을 내주어야 한다!! (나 이것땜에 한 시간 버렸음)

 

긴 설명은 필요없다. 예시로 살펴보자

CREATE TRIGGER UPDATE_COURSENUMB
AFTER UPDATE ON COURSE FOR EACH ROW
BEGIN
	UPDATE SECTION
	SET COURSE_NUMBER = 'SW0002'
	WHERE COURSE_NUMBER = 'SW0040';
END;

 

1) CREATE TRIGGER UPDATE_COURSENUMB : "UPDATE_COURSENUMB"란 TRIGGER를 생성

2) AFTER UPDATE ON COURSE FOR EACH ROW : COURSE 테이블이 업데이트 된다면 그 후 BEGIN, END 절을 시행

3) UPDATE 문 : 위의 Query임. 즉 SECTION의 Course_number가 'SW0040' 이라면 'SW0002'로 바꾼다는 의미

 

 

이렇게 하면 

 

SECTION 테이블과 COURSE 테이블의 Course_number가 모두 바뀐것을 확인할 수 있다!

(여기서 또 한시간 버림.. SQL Fiddle 이 온라인 DB tool에서는 계속 오류 발생! Live Oracle에서 하기! 버전이 달라서인 듯)

 

물론 TRIGGER가 일회성이긴 하지만 가장 빠르게 참조 무결성을 지키면서 UPDATE 할 수 있는 방법인 것 같다.