[DB] 외래키 (Foreign key)

자식이 부모의 행동을 참조하듯이, 참조 관계에서 참조 되는 테이블은 먼저 생성되어 있어야 하며, 해당 테이블을 부모 테이블이라고 부른다.

예를 들면, 학교 정보가 담긴 데이터베이스에 교사 정보 테이블이 있다고 하자.

교사 정보 테이블에서 과목 컬럼은 앞서 생성된 과목이라는 테이블을 참조한다.

(교사정보 -> 과목)

따라서 외래키가 선언된 참조하는 교사 정보 테이블을 자식 테이블이라고 하고, 참조 되는 과목 테이블을 부모 테이블이라고 부른다.

또 교사 정보 테이블의 과목 컬럼의 값은 과목 테이블의 값으로 제한된다.

 

 

 

subject 테이블과 teacher 테이블을 서로 외래키로 연결 시켜 보자.

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

CREATE TABLE subject(
	id serial primary key,
    sub_name varchar(10) not null
);

CREATE TABLE teacher(
	id serial primary key,
	tea_name varchar(10) not null,
	sub_code int references subject(sub_id),
	tea_certifi_date date
);

 

만약, 외래키를 여러개 설정 하고 싶다면 아래와 같이 하면 된다.

 

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

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

INSERT INTO subject(sub_name) values('math'),('sci'),('programming');

CREATE TABLE teacher(
	tea_id SERIAL PRIMARY KEY,
    tea_name VARCHAR(20) NOT NULL,
    sub_code INT NOT NULL,
    sub_name VARCHAR(20) NOT NULL,
    tea_certifi_date DATE NOT NULL,
    FOREIGN KEY(sub_code, sub_name) REFERENCES subject(sub_id, sub_name)
);


////////////////////////////////////////////////////////////////////////

 FOREIGN KEY(sub_code, sub_name) REFERENCES subject(sub_id, sub_name)
 이와 같이 여러개의 외래키를 등록 할 수 있다.

 

기본적으로 부모 테이블은 자식 테이블보다 먼저 삭제 또는 수정할 수 없다.

다만, 추가 조건을 부여한다면 삭제 또는 수정이 가능하다.

 

테이블을 지우는 상황을 ON DELETE라고 한다. 

ON DELETE는 5가지 유형이 있다.

1. ON DELETE NO ACTION
2. ON DELETE RESTRICT
3. ON DELETE SET NULL
4. ON DELETE CASCADE
5. ON DELETE SET DEFAULT

1,2번 -> 지우면 안되는 경우

3,4,5번 -> 지워야 하는 경우

 

컬럼을 지울 경우 기본적으로 ON DELETE NO ACTION속성이 디폴트로 설정 되어 있다.

이와 마찬가지로 ON DELETE RESTRICT 또한 참조된 컬럼을 지울 수 없다.

그렇다면, ON DELETE NO ACTION과 ON DELETE RESTRICT의 차이는 뭘까?

바로 트랜잭션 파트에서 그 차이를 알 수 있다.

(트랜잭션도 업로드 할 예정이다.)

 

위와 테이블과 동일해 보이지만, 이번에는 ON DELETE RESTRICT 조건을 넣어 테이블을 만들어 보자

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 RESTRICT,
    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');

 

이제 생성된 subject 테이블의 컬럼을 지워보자

역시 컬럼을 지울수가 없다.

디비를 다루다보면 지울수 없는 것을 지워야 하는 예외적인 상황이 생긴다.

그렇다면 이런 경우 어떻게 해야 할까?

이 때 사용할 수 있는 것이 바로 CASCADE, SET NULL , SET DEFAULT 조건들이다.

더 이상 내용을 추가 하면 글이 길어지니 이 내용들은 다음 게시글에 등록 하겠다.