[DB] 외래키2 (foreign key)

https://comingisspring.tistory.com/115

[DB] 외래키 (Foreign key)

 

 

위 내용에서 이어진 내용입니다.

 

1. ON DELETE CASCADE 

DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS subject;

CREATE TABLE subject(
	sub_id SERIAL PRIMARY KEY,
    sub_name VARCHAR(20) NOT NULL
);

INSERT INTO subject(sub_name) VALUES('math'),('sci'),('pro');

CREATE TABLE teacher(
	tea_id SERIAL PRIMARY KEY,
    tea_name VARCHAR(20) NOT NULL,
    sub_id INT REFERENCES subject ON DELETE CASCADE,
    tea_certifi_date DATE
);

INSERT INTO teacher(tea_name, sub_id, tea_certifi_date) VALUES('정선생', 1, '2020/03/12'),
('주선생', 2,'2022.05.13'),('이선생', 3,'2022-6-11');

 

기존 쿼리(ON DELETE RESTRICT -> ON DELETE CASCADE)로 변경 하였습니다.

이제 다시 부모테이블에서 컬럼을 삭제 해보자

 

부모테이블 (subject)

 

이번에는 해당 컬럼이 삭제 되었다.

그리고 이걸 참조 하는 자식테이블의 컬럼도 자동으로 지워졌다.

자식테이블 (teacher)

이처럼 ON DELETE CASCADE 조건은 참조한 열을 모두 지운다.

 

2. ON DELETE SET NULL

부모테이블에서 참조된 행이 삭제될 때 자식 테이블의 참조 행에서 해당 컬럼의 값을 자동으로 NULL로 셋팅한다.

DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS subject;

CREATE TABLE subject(
	sub_id SERIAL PRIMARY KEY,
    sub_name VARCHAR(20) NOT NULL
);

INSERT INTO subject(sub_name) VALUES('math'),('sci'),('pro');

CREATE TABLE teacher(
	tea_id SERIAL PRIMARY KEY,
    tea_name VARCHAR(20) NOT NULL,
    sub_id INT REFERENCES subject ON DELETE SET NULL,
    tea_certifi_date DATE
);

INSERT INTO teacher(tea_name, sub_id, tea_certifi_date) VALUES('정선생', 1, '2020/03/12'),
('주선생', 2,'2022.05.13'),('이선생', 3,'2022-6-11');

ON DELETE SET NULL 설정 하고 나서 부모테이블의 컬럼 삭제

부모테이블의 컬럼이 삭제 되고 나서 자식테이블의 컬럼을 보면 sub_id 컬럼에 아무런 데이터가 있지 않다(NULL).

이 조건을 ON DELETE SET NULL 이라 한다.

 

3. ON DELETE SET DEFAULT

이 조건은 부모 컬럼이 삭제 되면 자식 컬럼의 DEFAULT값으로 대체한다.

자식 테이블을 선언할 때 DEFAULT값을 설정하면, 부모 컬럼이 삭제 될 때 설정된 default값으로 변경된다.

그 외의 경우 NULL 값으로 변경된다.

 

DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS subject;

CREATE TABLE subject(
	sub_id SERIAL PRIMARY KEY,
    sub_name VARCHAR(20) NOT NULL
);

INSERT INTO subject(sub_name) VALUES('math'),('sci'),('pro');

CREATE TABLE teacher(
	tea_id SERIAL PRIMARY KEY,
    tea_name VARCHAR(20) NOT NULL,
    sub_id INT DEFAULT 2 REFERENCES subject ON DELETE SET DEFAULT,
    tea_certifi_date DATE
);

INSERT INTO teacher(tea_name, sub_id, tea_certifi_date) VALUES('정선생', 1, '2020/03/12'),
('주선생', 2,'2022.05.13'),('이선생', 3,'2022-6-11');

ON DELETE SET DEFAULT

부모 테이블의 컬럼을 삭제 하고 나서 자식 테이블의 컬럼을 보니, default값으로 설정 해 놓은 sub_id컬럼에 '2'가 들어 갔다.

주의 해야 할 점은, 디폴트로 설정된 값도 외래  키 제약조건을 만족해야 한다.

만약 위에서 작성한 디폴트값이 2가 아닌 5라고 하면, sub_id값이 null 이 아닌 5가 되어야 하는데 subject 테이블엔 1,2,3밖에 없으므로 참조되는 컬럼의 외래 키 제약조건을 위반하게 된다.

그렇다면, 주로 어디서 사용을 할까?

이 경우, 부모의 컬럼을 지우고 다른 컬럼으로 병합해야 할 때 쓰면 적합하다.