외래키 제약조건

외래키 제약조건

외래키란 다른 테이블 또는 데이터를 참조할 수 있도록 만들어진 제약조건을 말한다. 외래키를 사용하면 데이터 사이에 더 강한 연결을 구성할 수 있다. 크게 다른 테이블을 참조 하는 경우와 같은 테이블을 참조 하는 경우가 있다.

예시 : 두 테이블 간의 연결

게시글 정보와 댓글 정보를 관리하는 시스템을 구축하고 싶다고 가정하고 분석을 해보도록 한다. 두 개체는 다음과 같은 관계를 가진다.

게시글 한 개에 댓글 여러 개가 작성될 수 있으며, 게시글이 없이는 댓글이 작성될 수 없다.

이 경우 게시글과 댓글은 1:n 관계를 가진다고 표현한다. 하지만 게시글과 댓글이 같은 테이블에 저장될 수는 없기 때문에 나누어 저장하되 연결되어 있도록 구성하는 것이 필요하다.

게시글 테이블(board)

게시글 테이블은 특별한 항목 없이 번호, 제목, 내용 만으로 구성한다.

CREATE TABLE board(
board_no NUMBER PRIMARY KEY,
board_title varchar2(300) NOT NULL,
board_content varchar2(4000) NOT NULL
);

INSERT INTO board VALUES(1, '공지사항', '안내드립니다');
INSERT INTO board VALUES(2, '테스트', '테스트 글입니다');
INSERT INTO board VALUES(3, '안내', '신규 기능 안내입니다');

BOARD_NO	BOARD_TITLE	BOARD_CONTENT
1	공지사항	안내드립니다
2	테스트	테스트 글입니다
3	안내	신규 기능 안내입니다

댓글 테이블(reply) - 외래키 미설정

댓글 테이블은 게시글이 몇 개일지 모르기 때문에 하나로 구성하는 대신 게시글 번호를 보관하도록 구성한다.

CREATE TABLE reply(
reply_no NUMBER PRIMARY KEY,
reply_content varchar2(1000) NOT NULL
board NUMBER NOT NULL
);

INSERT INTO reply VALUES(101, '확인했습니다', 1);
INSERT INTO reply VALUES(102, '넵', 1);
INSERT INTO reply VALUES(103, '기대됩니다~', 3);
INSERT INTO reply VALUES(104, '오래 기다렸어요', 3);
INSERT INTO reply VALUES(105, '아싸!', 3);
INSERT INTO reply VALUES(106, '유령댓글', 5);

현재는 외래키를 설정하지 않은 상황이며, 저장까지 마친 뒤의 상황은 다음과 같다.

REPLY_NO	REPLY_CONTENT	BOARD
101	확인했습니다	1
102	넵	1
103	기대됩니다~	3
104	오래 기다렸어요	3
105	아싸!	3
106	유령댓글	5

문제점

두 테이블에 저장된 데이터를 연결해보면 다음과 같다.

1. 5번 게시글은 존재하지 않는데 이를 참고하는 댓글이 존재하게 되므로 구조 상 올바른 데이터가 저장되었다고 보기 어려운 상황이다.

2. 게시글이 삭제되어도 댓글은 남아있게 되므로 데이터 관리가 어려워지며 추가 처리가 필요하다.

위의 문제점들을 해결하기 위해서 외래키(Foreign key)라는 것이 필요하다.

댓글 테이블(reply) - 외래키 설정

댓글 테이블에 외래키를 설정하여 게시글 테이블과 강한 연결이 되도록 테이블을 구성다.

CREATE TABLE reply(
reply_no NUMBER PRIMARY KEY,
reply_content varchar2(1000) NOT NULL
board REFERENCES board(board_no)
);

REFERENCES boadr(board_no) 를 추가하면 board 테이블의 board_no 항목을 참조하겠다는 뜻이 되어 종속 관계가 성립한다. 따라서 참조하는 항목에 존재하지 않는 데이터는 설정할 수 없다. 즉, 다음 코드는 외래키를 설정한 시점부터 등록이 불가다.

INSERT INTO reply VALUES(106, '유령댓글', 5);

외래키를 설정함으로 인하여 1번 문제점을 해결하였다.

외래키 연결 조건

외래키는 생성 시 다양한 연결 조건을 설정할 수 있다. 이는 2번 문제점을 해결하는 것에 도움을 준다.

• ON DELETE SET NULL

• ON DELETE CASCADE

• ON DELETE CASCADE CONSTRIANT

외래키 연결 조건 미설정

다음과 같이 작성할 경우 board 테이블 데이터 삭제 시 연결된 하위 데이터가 존재할 경우 삭제가 불가능하다.

board REFERENCES board(board_no)

외래키 CASCADE 설정

다음과 같이 작성할 경우 board테이블 데이터 삭제 시 연결된 하위 데이터가 삭제된니다.

board REFERENCES board(board_no) ON DELETE CASECADE

외래키 SET NULL 설정

다음과 같이 작성할 경우 board테이블 데이터 삭제 시 연결된 하위 데이터의 항목이 null로 설정된다.

board REFERENCES board(board_no) ON DELETE SET NULL

상황에 따라 알맞은 연결 조건을 사용하는 것이 중요하다.

회원과 댓글이 있을 때, ON DELETE SET NULL 조건을 이용하여 회원이 탈퇴해도 댓글이 남아있도록 설정할 수 있다.

예시 : 한 테이블 사이의 연결

직원 테이블을 만든다고 할 때, 외래키를 이용하여 직원 간의 상하관계를 설정할 수 있다.

CREATE TABLE employee(
emp_no NUMBER PRIMARY KEY,
emp_name VARCHAR2(21) NOT NULL,
emp_rank VARCHAR2(12) NOT NULL CHECK(emp_rank IN ('사장','임원','부장','차장','과장','대리','사원','인턴')),
senior_no REFERENCES employee(emp_no) ON DELETE SET NULL
);

INSERT INTO employee VALUES(1, '민준', '사장', null);
INSERT INTO employee VALUES(2, '서준', '임원', 1);
INSERT INTO employee VALUES(3, '서연', '임원', 1);
INSERT INTO employee VALUES(4, '지우', '부장', 2);
INSERT INTO employee VALUES(5, '민서', '부장', 2);
INSERT INTO employee VALUES(6, '도윤', '과장', 3);
INSERT INTO employee VALUES(7, '지호', '대리', 5);
INSERT INTO employee VALUES(8, '채원', '대리', 6);
INSERT INTO employee VALUES(9, '윤서', '사원', 8);
INSERT INTO employee VALUES(10, '수아', '사원', 8);

데이터를 구조화하면 다음과 같다.

EMP_NO	EMP_NAME	EMP_RANK	SENIOR_NO
1	민준	사장	-
2	서준	임원	1
3	서연	임원	1
4	지우	부장	2
5	민서	부장	2
6	도윤	과장	3
7	지호	대리	5
8	채원	대리	6
9	윤서	사원	8
10	수아	사원	8

게시글의 경우도 이러한 계층 구조를 이용하여 구성될 수 있다. 이러한 구조를 계층형 구조라고 다.