[MySQL] 3. 테이블 정규화와 외래키

 

JS 풀스택 웹개발로 진로를 정하면서 주로 사용했던 몽고DB, 즉 NoSQL은 조인과 정규화라는 작업이 존재하지 않았다. 정확히는 존재했지만 RDBMS처럼 까다롭지 않았다... 한개의 테이블이라고 할 수 있는 한 개의 Collection에 관련된 항목을 JOSN key값으로 설정해주고, 만일 다른 테이블에 있는 내용을 함께 저장하는 JOIN이 필요할 경우 ref:테이블이름 을 적기만 하면 끝나는 단순한 형태였다. 너무나 쉽다는것은 때때로 단점이 되기도 한다. 너무 쉬워서... 그것만 사용하다 보니 관계형 데이터베이스를 일정부분 까먹게 되는 것이다... 

 

따라서 다시는 잊지 않기 위해 중요한 개념인 정규화와 외래키에 대해 실습하고, 조인시킨 데이터를 DML로 다루어본다. 예시를 직접 만드는게 정말 쉬운일이 아닌걸 깨달을 수 있다..

 

---

 

2정규화

정처기를 공부할 때 정규화를 '원부이 결다조' 로 외웠었다.

1정규형은 말 그대로 '원자값'으로 구성되어야 한다는 뜻이다. 한개의 컬럼은 한 개의 값만을 가져야 한다. 1정규형은 간단하기 때문에 실습은 없이 진행한다.

 

2정규형은 '부', 즉 '부분 함수 종속'을 제거해야 한다는 뜻이다. 부분 함수 종속을 알기 위해서는 복합키를 알아야 한다. Composite Primary Key를 복합키라고 하는데, 두 개 이상의 컬럼을 PK로 지정한 것을 복합키라고 한다. 

릴레이션에서 기본키가 복합키일 경우, 기본키를 구성하는 컬럼 중 하나에게만 종속된 컬럼이 존재하는 것을 '부분함수 종속' 이라고 한다. 예시를 보자.

 

'연락번호' 이라는 테이블이 있고, '회원번호, 회원이름, 휴대폰번호, 통신사'라는 컬럼이 있다. 집전화를 이용하기 위해서는, 전화를 사용하는 개인이 각각 N분의 1로 더치페이해야 한다고 가정했다.

---

연락번호 테이블

회원번호	회원이름	집 전화번호	통신사	개인 요금 납부
001	A	02-111-1111	SKT	O
002	B	02-222-2222	KT	O
003	C	02-333-3333	KT	X
003	C	053-444-4444	LG	O
004	D	02-333-3333	KT	O

- [회원번호]와 [집 전화번호]가 모두 있어야 PRIMARY KEY 역할을 수행할 수 있다. 

- 따라서 [회원번호]와 [집 전화번호]는 Composite Primary key 이다.

- [통신사]는 [회원번호]와는 상관 없이, [집 전화번호]에만 종속되어있는 컬럼이다.

- 이렇게 두 개의 기본키 [회원번호] 및 [집 전화번호] 모두에게 종속되어 있지 않고, 일부인 [집 전화번호] 기본키 컬럼에만 종속되어 있는 [통신사] 컬럼을 'Partial Dependency가 있다', 즉 '부분 함수 종속' 되어 있다고 한다.

---

 

부분함수 종속을 제거해주기 위해서는 종속된 컬럼을 분리해 주어야 한다.

집 전화번호	통신사
02-111-1111	SKT
02-222-2222	KT
02-333-3333	KT
053-444-4444	LG

회원번호	회원이름	집 전화번호	개인 요금 납부
001	A	02-111-1111	O
002	B	02-222-2222	O
003	C	02-333-3333	X
003	C	053-444-4444	O
004	D	02-333-3333	O

 

 

그렇다면 위 테이블에서 [개인요금납부] 컬럼은 2정규형을 만족하기 위해 분리해주어야 하는 테이블에 속할까? 그렇지 않다. 요금납부 컬럼은 회원번호나 집 전화번호 둘 중 하나에 종속되어 있지 않고, 회원번호 집 전화번호 모두에 종속되어 있기 때문이다.

 

 

제3정규화

 

3정규형은 이행적 종속성을 제거하는 것을 뜻한다. 위의 예제에서 2정규화를 마친 테이블에, 영업사원과 영업사원의 성별을 나타내는 컬럼을 추가하였다. 해당 번호를 사용하고 있는 전화를 영업한 사원과 성별을 기록한 것이다.

---

집 전화번호	통신사	영업사원	성별
02-111-1111	SKT	유재석	남자
02-222-2222	KT	광희	남자
02-333-3333	KT	송지효	여자
053-444-4444	LG	송지효	여자

- [성별] 컬럼은 [영업사원] 테이블에 종속한다.

- [영업사원] 컬럼은 기본키가 아닌 컬럼이다.

---

 

3정규형을 하려면 일반 컬럼에만 종속된 컬럼을 분리해 주면 된다.

집 전화번호	통신사	영업사원
02-111-1111	SKT	유재석
02-222-2222	KT	광희
02-333-3333	KT	송지효
053-444-4444	LG	송지효

영업사원	성별
유재석	남자
광희	남자
송지효	여자

 

다른 테이블의 데이터를 사용할 경우, primary key를 사용하는 것이 기본적인 원칙이다. 이를 '외래키' 라고 한다. 테이블을 작성할 때 첫번째 컬럼을 primary key로 설정해 주는 것이 좋다. 

 

id	영업사원	성별
01	유재석	남자
02	광희	남자
03	송지효	여자

 

 

이전까지 실습했던 데이터에 레코드를 추가하여, 외래키를 실습하였다. 먼저 pet 데이터베이스의 aboutpet에 다섯개의 레코드를 추가해준다.

 

 

그리고 user에 pet 컬럼을 생성하여, 각각 1,2,3,4,5라는 값을 주었다.

 

외래키를 사용하고자 하는 테이블에서 Foreign Keys를 클릭하고, 우클릭으로 Create New Foreign Key를 클릭하면 이미 작성해놓은 컬럼을 외래키 속성으로 등록해줄 수 있다.

 

pet 데이터베이스의 aboutpet 테이블에 있는, id 컬럼을 가져와 외래키로 사용할 것이다. 해당 컬럼은 user 테이블의 pet 컬럼값으로 사용된다.

 

확인을 누르면 아래와 같이 참조 테이블과 개체가 잘 설정된것을 볼 수 있다.

 

외래키 설정이 완료되면 화살표 마크가 생긴다. 클릭으로 곧장 해당 컬럼이 존재하는 원래의 테이블, 즉, aboutpet 테이블로 이동할 수 있다.

 

SQL 명령어로도 외래키를 만들 수 있다. CREATE와 ALTER문으로 만들어 줄 수 있는데, 외래키의 특성 때문에 다양한 사정을 고려해 주어야 한다.

 

만일 외래키로 참조되고 있는 테이블에서 데이터 삭제, 또는 변경과 같은 행동이 일어날 경우 함부로 데이터를 변경하거나 삭제할 수 없게 도와준다. 따라서 명령어를 통하여 삭제나 변경이 일어날 때 수행할 동작을 설정해주어야 한다.

 

참조되는 테이블의 값이 삭제될 경우에는 ON DELETE, 수정될 경우에는 ON UPDATE문으로 설정할 수 있다.

 

• CASCADE : 만일 참조되는 테이블에서 데이터의 삭제나 수정이 일어날 경우, 해당 데이터를 참조하는 테이블에서도 함께 삭제와 수정이 일어나도록 해주는 명령어이다.
• SET NULL : 참조되는 테이블에서 데이터를 삭제하거나 수정할 경우, 참조하는 테이블의 데이터는 NULL로 변경된다.
• NO ACTION : 참조되는 테이블의 데이터가 변경되거나 삭제되더라도, 참조하는 테이블의 데이터가 변경되지 않는다.
• SET DEFAULT : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 필드의 기본값으로 설정된다.
• RESTRICT : 참조하는 테이블에 데이터가 남아 있으면, 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없다.

 

아래의 명령어들은 '참조되는 테이블에서 데이터의 수정이 일어나면, 참조하는 테이블의 데이터 또한 함께 수정하도록 하고, 참조되는 테이블의 데이터를 삭제하려는 경우에는 해당 행동을 할 수 없게 한다' 는 제약 조건으로 외래키를 생성하는 명령어이다.

CREATE TABLE 테이블이름 (     필드이름 필드타입,     ...,     FOREIGN KEY (필드이름)     REFERENCES 테이블이름 (필드이름) ON UPDATE CASCADE ON DELETE RESTRICT )

ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] FOREIGN KEY (필드이름) REFERENCES 테이블이름 (필드이름) ON UPDATE CASCADE ON DELETE RESTRICT

 

외래키를 삭제할 때에는 DROP문을 사용한다.

ALTER TABLE 테이블이름 DROP FOREIGN KEY 제약조건이름