[SQL] ON DELETE CASCADE - PK를 참조 중인 FK행 자동 삭제

PK 를 참조중인 FK 값이 있는 행이

부모 PK 가 삭제될 때 자동으로 함께 삭제되도록 하는 코드를 확인해보자.

 

 

📁 1. CREATE TABLE, SEQUENCE

PK를 직접 지정하지 않고 자동으로 생성되도록 데이터가 들어갈 테이블과 시퀀스를 생성해준다.

-- CREATE TABLE
CREATE TABLE TBL_MEMBER(
	MEMBER_ID NUMBER,
	MEMBER_NAME VARCHAR2(500) NOT NULL,
	MEMBER_AGE NUMBER NOT NULL,
	MEMBER_PHONE VARCHAR2(500) NOT NULL,
	MEMBER_ADDRESS VARCHAR2(500) NOT NULL,
	CONSTRAINT PK_MEMBER PRIMARY KEY(MEMBER_ID),
	CONSTRAINT UK_MEMBER UNIQUE(MEMBER_PHONE)
);
CREATE TABLE TBL_BOOK(
	BOOK_ID NUMBER,
	BOOK_NAME VARCHAR2(500) NOT NULL,
	BOOK_GENRE VARCHAR2(500),	
	MEMBER_ID NUMBER,
	CONSTRAINT PK_BOOK PRIMARY KEY(BOOK_ID),
	CONSTRAINT FK_BOOK FOREIGN KEY(MEMBER_ID)
	REFERENCES TBL_MEMBER(MEMBER_ID),
	CHECK(BOOK_GENRE IN ('인문학', '추리', 'IT', '로맨스'))
);

-- CREATE SEQUENCE
CREATE SEQUENCE SEQ_BOOK;
CREATE SEQUENCE SEQ_MEMBER;

 

 

📁 2. INSERT 데이터 (SEQUENCE 이용)

시퀀스를 생성했으면,

시퀀스를 이용해서 데이터를 넣도록 한다.

-- 시퀀스를 사용하여 데이터 넣기
-- 회원 정보 추가
INSERT INTO TBL_MEMBER
VALUES(SEQ_MEMBER.NEXTVAL, '홍싸리', 22, '010-1111-1222', '수원시 장안구');

INSERT INTO TBL_MEMBER
VALUES(SEQ_MEMBER.NEXTVAL, '홍길동', 23, '010-2222-2222', '수원시 장안구');

INSERT INTO TBL_MEMBER
VALUES(SEQ_MEMBER.NEXTVAL, '강감찬', 25, '010-3333-3333', '수원시 장안구');


-- 책 정보 추가
INSERT INTO HR.TBL_BOOK
(BOOK_ID, BOOK_NAME, BOOK_GENRE)
VALUES(SEQ_BOOK.NEXTVAL, '셜록 홈즈', '추리');

INSERT INTO TBL_BOOK
(BOOK_ID, BOOK_NAME, BOOK_GENRE)
VALUES(SEQ_BOOK.NEXTVAL, 'DBMS 완전 정복', 'IT');

INSERT INTO TBL_BOOK
(BOOK_ID, BOOK_NAME, BOOK_GENRE)
VALUES(SEQ_BOOK.NEXTVAL, '그리고 아무도 없었다', '추리');
SELECT * FROM TBL_MEMBER;

 

 

생성된 테이블의 모습

TBL_BOOK

TBL_MEMBER

 

📁 3. 책 대여 데이터 넣어주기

--	책 대여 하기
UPDATE TBL_BOOK 
SET MEMBER_ID = 1
WHERE BOOK_ID = 1;

UPDATE TBL_BOOK 
SET MEMBER_ID = 1
WHERE BOOK_ID = 2;

UPDATE TBL_BOOK 
SET MEMBER_ID = 3
WHERE BOOK_ID = 3;

 

 

📁 4. 관계를 맺은 테이블 간 데이터 삭제

자, 필요한 데이터를 다 넣었으니 이제

서로 관계를 맺은 테이블 안의 데이터를 삭제해보려고 한다.

DELETE FROM TBL_MEMBER
WHERE MEMBER_ID = 1; -- 오류 발생!

 

TBL_MEMBER 에서 MEMBER_ID 값이 1 인 데이터를 삭제하려고 하면 오류가 발생한다.

 

child record가 발견되었다고 한다.

FK_BOOK 이 있기 때문이다.

 

이제 본격적으로 삭제를 하기 위한 작업을 진행한다.

 

 

 

일단 아래의 과정을 거치려고 한다.

1. 자식에서 해당 FK 값을 NULL 로 수정

2. 자동으로 부모 PK 값이 삭제되면 자식에서 해당 PK 값을 참조하고 있던 행도 함께 삭제하도록

    FK 제약 조건 뒤에 ON DELETE CASCADE 옵션을 추가한다.

 

ALTER TABLE TBL_BOOK DROP CONSTRAINT FK_BOOK;

일단 DROP CONSTRAINT 를 작성하여 FK_BOOK 제약 조건을 삭제해주고 새롭게 다시 부여해준다.

이렇게 제약 조건을 삭제하면

Constraints 탭에서 FK_BOOK 가 사라진다.

 

이랬던 엔티티 관계도도

이렇게 변한다. (관계가 끊겼기 때문)

 

 

 

 

근데 우린 관계를 이어주어야하니까 다시 후다닥 연결해주도록 한다.

대신! 이번에는 ON DELETE CASCADE 를 함께 작성해주어

참조하고 있는 부모 테이블의 PK 값이 삭제되면 참조하고 있던 자식 행도 전부 삭제되도록 한다.

ALTER TABLE TBL_BOOK ADD CONSTRAINT FK_BOOK FOREIGN KEY(MEMBER_ID)
REFERENCES TBL_MEMBER(MEMBER_ID) ON DELETE CASCADE;

 

 

 

자 이제 아까 에러가 났었던 DELETE 쿼리문을 실행시키면 오류 없이 작동이 될 것이다.

DELETE FROM TBL_MEMBER
WHERE MEMBER_ID = 1; -- 다시 실행하면 에러가 나지 않고 4번 회원이 가지고 있던 정보까지 전부 삭제됨

 

 

 

 

MEMBER_ID 가 1인 데이터를 삭제했더니

 

 

 

 

MEMBER_ID 를 참조하고 있던 자식 테이블의 행들도 전부 함께 삭제된 것을 확인할 수 있다.