제 3 정규형(3NF) Foreign Key

제3정규형 (3rd normal form)

 

제2정규형을 만족하는 테이블에서 primary key 또는 composite primary key 컬럼에 종속된게 아닌

컬럼에 종속된 컬럼을 다른 테이블로 빼면 그게 제3정규형 테이블입니다. 

 

[체육센터 프로그램 table]

프로그램	가격	강사	출신대학
스쿼시	5000	김을용	서울대
헬스	6000	박덕팔	연세대
골프	8000	이상구	고려대
골프 중급	9000	이상구	고려대
개인피티	6000	박덕팔	연세대

 

primary key는 프로그램이다 근대 출신대학은 프로그램과는 상관이 없고 '강사'라는 테이블과 연관이있다

그래서 출신대학을 다른테이블로 빼면 제3정규형이다

 

[체육센터 프로그램 table]

프로그램	가격	강사
스쿼시	5000	김을용
헬스	6000	박덕팔
골프	8000	이상구
골프 중급	9000	이상구
개인피티	6000	박덕팔

 

[강사정보 table]

강사	출신대학
김을용	서울대
박덕팔	연세대
이상구	고려대

 

강사정보 테이블만 수정해주면 '체육센터 프로그램테이블' 전체에 영향이가서 수정이 편해진다

 

 transitive dependency

 

여러분은 배운 사람이니까 정확한 정의도 알려드리자면

제2정규형 테이블에서 transitive dependency도 제거해버리면 그게 제3정규형입니다. 

transitive dependency는 

 

 

 

1. composite primary key 또는 primary key 역할을 하는 컬럼이 있고  

2. 거기에 직접 종속된 컬럼A가 있고 

3. 또 거기에 직접 종속된 하찮은 컬럼B가 있으면 

컬럼B가 primary key 컬럼에 transitive dependency 가 있다고 표현합니다. 

그래서 그 컬럼B를 다른 테이블로 옮기라는 소리입니다. 

옮기면 제3정규형을 만족하는 테이블이 완성됩니다. 

 

제4, 5, 6 정규형도 있는데 대부분은 3까지만 하면 대부분의 중복문제는 해결할 수 있기 때문에

여유가된다면 찾아보도록 하자. 

 

primary key, foreign key 

항상 해주는게 좋다!!

정규화하려고 테이블을 쪼갤 때가 많습니다. 그 경우 주의점은 

① 테이블마다 항상 primary key 역할을 하는 컬럼을 넣어두는게 좋습니다. 'id' 컬럼 이런거요 

② 다른 테이블에 있는 컬럼내용을 가져다쓸 때도 primary key를 사용하는게 좋습니다. 

 

 

 

 

▲ 예를 들어서 아까처럼 제3정규화 하느라 이렇게 테이블을 분리해놨다고 칩시다.

이 테이블을 쓸만하게 고쳐보면 

 

 

 

 

 

① 테이블마다 primary key 역할을 할 수 있는 id 컬럼같은거 하나 만들어두면 좋습니다.

그래서 만들어봤습니다. 

 

Q. composite primary key가 이미 있는데요?

A. 그거써도 되는데 id 컬럼 만들어두면 나중에 SELECT로 원하는 행만 찾을 때 편리합니다. 

 

② 다른 테이블에 있는 컬럼내용을 가져다쓸 때도 primary key를 사용하는게 좋습니다.

지금 왼쪽 테이블보면 '강사' 컬럼이 있습니다.

거기에 강사 이름이 들어가야하는데 강사 정보는 오른쪽 테이블에 들어있군요.

그럴 경우 강사 이름을 그대로 적는것 보다는 강사의 id를 적는게 좋습니다.

이러면 나중에 동명이인 강사가 생겨도 명확히 구분할 수 있으니까요.

 

저기서 강사id 컬럼을 전문용어로 foreign key 라고 합니다.

어려운건 아니고 다른 테이블에 있던 primary key를 다른 테이블에 사용할 때는 foreign key라고 부릅니다. 

참고로 알아둡시다. 

 

Foreign key 등록하는 법

 

실제 컬럼에다가 "이 컬럼은 foreign key 역할이다"라고 등록할 수도 있습니다. 

그럼 장점이 몇개 있어서 그거 하는 법을 알아봅시다. 

 

 

CREATE TABLE 테이블명 (
    id INT PRIMARY KEY,
    프로그램 VARCHAR(100),
    강사id INT REFERENCES 다른테이블(다른테이블컬럼)
)

컬럼만들 때 REFERENCES 다른테이블명(컬럼명) 잘 입력해주면 됩니다. 

 

CREATE TABLE 테이블명 (
    id INT,
    프로그램 VARCHAR(100),
    강사id INT,
    CONSTRAINT 제약조건작명 PRIMARY KEY (id), 
    CONSTRAINT 제약조건작명2 FOREIGN KEY (강사id) REFERENCES 다른테이블(다른테이블컬럼),
)

아니면 가장 하단에 CONSTRAINT 문법써도 된다고 했으니 그거 써도 됩니다. 

 

 

ALTER TABLE 테이블명 ADD 
CONSTRAINT 제약조건작명 FOREIGN KEY (강사id) REFERENCES teacher(id) 

이미 생성된 테이블을 수정하고 싶으면 ALTER TABLE 테이블명 ADD 입력하고

뒤에 CONSTRAINT 문법 쓰면 됩니다. 

그래서 위처럼 작성하면 이미 존재하는 테이블 컬럼에 foreign key 제약걸기 가능