외래키(FOREIGN KEY) 설정하기

외래키를 설정하는 이유는 무결성을 채우기 위함입니다.
관계성 있는 테이블들끼리 외래키를 설정을 하면
쓰레기 데이터들이 쌓이지 않겠죠.

* 실습하기 *

create table 시험
(시험번호 int constraint PK_시험_시험번호 primary key, 시험명 char(20))

insert into 시험 values(10, '국어')
insert into 시험 values(20, '영어')
insert into 시험 values(30, '수학')

create table 응시자
(
 번호 int IDENTITY (1,1)
, 이름 char(10)
, 시험번호 int
)

insert into 응시자 values('오버탑', 10)
insert into 응시자 values('오버탑', 20)
insert into 응시자 values('오버탑', 30)
insert into 응시자 values('오버탑', 40)

여기까지 데이터들이 생성되었다가 가정하자

그럼 외래키를 설정해보자

1.T-SQL 에서 생성하기

alter table 응시자
add constraint FK_응시자_시험번호
 foreign key(시험번호) references 시험(시험번호)
-- on update cascade
-- on delete cascade

여기서잠깐??
on update cascade -- 옵션 1
on delete cascade --옵션 2

 cascade : 설정 체크
 no action : 설정 체크해제

 - 에러메세지 -
서버: 메시지 547, 수준 16, 상태 1, 줄 1
ALTER TABLE 문이 COLUMN FOREIGN KEY 제약 조건
'FK_응시자_시험번호'과(와) 충돌되었습니다.
충돌은 'PushUser' 데이터베이스, '시험', column '시험번호'
테이블에서 발생했습니다.

왜 생겼을까여 ??
답 : 참조하는 응시자 테이블의 시험번호중에 참조당하는 시험 테이블에 없어서 생긴다. (시험번호 40)

delete from 응시자 where 시험번호 = 40

삭제 후에 alert 문을 실행하면 외래키가 생성이 된다.

TIP. 제약조건 삭제는

 alter table 응시자
 drop FK_응시자_시험번호

2. EM 에서 생성하기

 

TIP. 옵션설정.
  파란색 박스 1 : on update cascade -- 옵션 1
  파란색 박스 2 : on delete no action --옵션 2

 

각 옵션에 따라 테스트 해볼까여??
참조자(응시자자)는 모든 액션이 실행되지만 참조당하는자(시험)은
외래키 제약조건에 오류가 발생합니다.

select * from 시험
select * from 응시자

// 참조자에 해당하는 값이 있을 경우에 수정/삭제하기
-- update 시험 set 시험번호 = 50 where 시험번호 = 10
-- delete from 시험 where 시험번호 = 10
- 에러메세지 -
서버: 메시지 547, 수준 16, 상태 1, 줄 1
UPDATE 문이 COLUMN REFERENCE 제약 조건 'FK_응시자_시험'과(와)
충돌되었습니다. 충돌은 'PushUser' 데이터베이스, '응시자', column
'시험번호' 테이블에서 발생했습니다. 문이 종료되었습니다.

// 참조자에 해당하는 값이 없을 경우에 수정/삭제하기

delete from 응시자 where 시험번호 = 30

update 시험 set 시험번호 = 300 where 시험번호 = 30
 - 정상업데이트
select * from 시험

delete from 시험 where 시험번호 = 300
 - 정상삭제

참조자는 언제든지 수정/업데이트가 가능하다.
update 응시자 set 시험번호 = 10 where 시험번호 = 20

// 참조당하는자(시험)를 수정, 삭제가 가능하게 하려면 옵션을 추가하면 된다.

제약조건을 삭제하고 재 설정을 한다.

 alter table 응시자
 drop FK_응시자_시험

 alter table 응시자
 add constraint FK_응시자_시험번호
  foreign key(시험번호) references 시험(시험번호)
  on update cascade
  on delete cascade

select * from 시험
select * from 응시자

update 시험 set 시험번호 = 100  where 시험번호 = 10

select * from 시험
select * from 응시자

시험테이블과 응시자테이블의 시험번호가 동시에 바뀐다. 확인하셨나여 ?

delete 시험 where 시험번호 = 100
select * from 시험
select * from 응시자

이상 옵션에 따라 참조가의 데이터값들의 변화를 알아봤습니다.

결론. 외래키는 데이터 무결성을 위함입니다. DB모델링시 필요한
부분들은 미리 설정을 하셔서 최적화된 데이터를 이용하시면 좋을 것같으네여.