MySQL06 Foreign Key
비트캠프 서초본원 엄진영 강사님의 수업을 듣고 정리했습니다.
FK(Foreign Key)
다른 테이블의 PK를 참조하는 컬럼
1.테이블 및 데이터 준비
테이블 생성
- 게시판 테이블 생성
create table test1(
no int not null primary key auto_increment,
title varchar(255) not null,
content text,
rdt datetime not null default now()
);
- 첨부 파일 테이블 생성
create table test2(
fno int not null primary key auto_increment, /* 첨부파일 고유번호 */
filepath varchar(255) not null, /* 파일시스템에 저장된 첨부파일의 경로 */
bno int not null /* 게시판 번호 */
);
데이터 입력
- 게시판 데이터 입력
insert into test1(title) values('aaa');
insert into test1(title) values('bbb');
insert into test1(title) values('ccc');
insert into test1(title) values('ddd');
insert into test1(title) values('eee');
insert into test1(title) values('fff');
insert into test1(title) values('ggg');
insert into test1(title) values('hhh');
insert into test1(title) values('iii');
insert into test1(title) values('jjj');
- 첨부파일 데이터 입력
insert into test2(filepath, bno) values('c:/download/a.gif', 1);
insert into test2(filepath, bno) values('c:/download/b.gif', 1);
insert into test2(filepath, bno) values('c:/download/c.gif', 1);
insert into test2(filepath, bno) values('c:/download/d.gif', 5);
insert into test2(filepath, bno) values('c:/download/e.gif', 5);
insert into test2(filepath, bno) values('c:/download/f.gif', 10);
2. FK 제약 조건이 없을 때
- 첨부파일 데이터를 입력할 때 존재하지 않는 게시물 번호가 들어 갈 수 있다.
- 그러면 첨부파일 데이터는 무효한 데이터가 된다.
insert into test2(filepath, bno) values('c:/download/x.gif', 100);
- 첨부 파일이 있는 게시물을 삭제할 수 있다.
- 마찬가지로 해당 게시물을 참조하는 첨부파일 데이터는 무효한 데이터가 된다.
delete from test1 where no=1;
무효한 데이터 생성
- 다른 테이블의 데이터를 참조하는 경우, 참조 데이터의 존재 유무를 검사하지 않는다.
- 테이블의 데이터를 삭제할 때 다른 테이블이 참조하는지 여부를 검사하지 않기 때문에 무효한 데이터가 생성된다.
해결책
- 다른 데이터를 참조하는 경우 해당 데이터의 존재 유무를 검사하도록 강제한다.
- 다른 테이터에 의해 참조되는지 여부를 검사하도록 강제한다.
- 이것을 가능하게 하는 문법이 “외부키(Foreign Key)” 이다.
FK(foreign key) 제약 조건 설정
- 다른 테이블의 데이터와 연관된 데이터를 저장할 때 무효한 데이터가 입력되지 않도록 하는 문법
- 다른 테이블의 데이터가 참조하는 데이터를 임의의 지우지 못하도록 하는 문법
- 그래서 데이터의 무결성(data integrity; 결함이 없는 상태)을 유지하게 도와주는 문법
제약 조건 설정
- 다른 테이블의 PK를 참조하는 컬럼으로 선언한다.
- 테이블B의 컬럼b가 테이블A의 컬럼a를 참조한다.
alter table 테이블명B
add constraint 제약조건이름 foreign key (컬럼명b) references 테이블명A(컬럼명a);
/* 제약조건이름은 보통 '테이블명_테이블명_fk'로 한다. */
- 기존에 테이블에 무효한 데이터가 있을 수 있으면 FK 조건을 설정할 수 없다.
- 이 경우, 기존 테이블을 삭제하고 새로운 테이블을 만든 후 FK 조건을 설정한다.
alter table test2
add constraint test2_bno_fk foreign key (bno) references test1(no);
/* bno는 다른 테이블의 key 값을 저장하는 컬럼 */
데이터 입력
- test2 테이블에 FK 제약 조건을 건 후 데이터 입력
- 1번 게시물이 존재하지 않기 때문에 데이터를 입력할 수 없다 .
insert into test2(filepath, bno) values('c:/download/a.gif', 1);
insert into test2(filepath, bno) values('c:/download/b.gif', 1);
insert into test2(filepath, bno) values('c:/download/c.gif', 1);
- 5번, 10번 게시물은 존재하기 때문에 첨부파일 데이터를 입력할 수 있다.
insert into test2(filepath, bno) values('c:/download/d.gif', 5);
insert into test2(filepath, bno) values('c:/download/e.gif', 5);
insert into test2(filepath, bno) values('c:/download/f.gif', 10);
데이터 삭제
- 게시글 삭제하기
- 2번 게시물은 test2 테이블의 데이터들이 참조하지 않기 때문에 마음대로 지울 수 있다.
delete from test1 where no=2; /* OK! */
- 그러나 5번 게시물은 삭제할 수 없다.
- 왜? test2 테이블의 데이터 중 일부가 참조하기 때문이다.
delete from test1 where no=5; /* Error! */
- 5번 게시글을 삭제하려면, 5번 게시글을 참조하는 데이터를 삭제하고 5번 게시글을 삭제한다.
delete from test2 where bno=5;
delete from test1 where no=5; /* OK! */
- 테이블 삭제하면 테이블에 걸려 있던 제약 조건도 모두 삭제된다.
- 같은 이름의 테이블을 다시 만들면, 제약 조건도 모두 새로 걸어야 한다.
3. FK 제약 조건 확인 & 삭제
제약 조건 확인
- FK 확인
select * from information_schema.table_constraints;
- 테이블 기준으로 FK 확인
select * from information_schema.table_constraints where table_name = '테이블명';
- 테이터 베이스 기준으로 FK 확인
select * from information_schema.table_constraints where constraint_schema = '데이터베이스명';
제약 조건 삭제
- 제약조건 삭제하기
alter table 테이블명 drop 제약조건명;
- FK 제약 조건 삭제하기
alter table 테이블명 drop foreign key 제약조건명;
4. 용어 정리
부모 테이블
- test1 처럼 다른 테이블에 의해 참조되는 테이블
자식 테이블
- test2 처럼 다른 테이블의 데이터를 참조하는 테이블