[MySQL] 6장 외래 키 설정하기, 1NF (테이블 쪼개기),

이제 지금 까지 배웠던 내용을 실제로 실습해 볼 것이다.

1. 제약조건 참조키 설정, 테이블 연산으로 값 채우기
2. 1NF 실습

1. 제약조건 참조키(Foreign Key) 설정

1장에서 공부한 student 테이블을 생성하자. 이미 student 테이블이 있다면 DROP 후 재 생성하는 것을 추천한다.

현재 student 테이블이 있으니 학과를 나타내는 department 테이블을 생성한다. 학과의 종류는 IDE, CSE, ESE, MSE, IME 이렇게 5 종류다.

첫 번째 쿼리 : department 테이블 생성

7~9 : SELECT를 통해 가져온 결과를 바로 테이블에 INSERT 한다.

7 : department 테이블의 department_code에 값을 넣는다.

8 : student 테이블의 belong값을 belong의 값 순서로 정렬하여

그러나 우리가 의도한 결과가 안나올 것이다.

위 결과를 보면 같은 값이 여러 행에 거쳐 나온다. 우리는 중복되지 않게 보고 싶다.

  이제 원하는 결과가 나온다. DISTINCT는 해당 열의 값이 중복되지 않게 SELECT 한다.

 

4 : AS로 CREATE 직후 SELECT 로 가져온 결과를 테이블 생성 후 바로 채울 수 있다.

또 중복을 제거 했기 때문에 department_code는 유일한 값이 된다. 그렇기 때문에 department2 테이블의 기본 키는 department_code가 된다.

이제 student 테이블의 belong이 department 테이블의 department_code를 참조하도록 설정해보자.

테이블 설정에 대한 내용은 2장에서 다룬바 있다.

2 : 제약조건 student_department를 추가한다. student_department는 내가 추가할 임의의 제약조건 이름이다.

3 : belong 을 외래키로 설정할 껀데

4 : department2 테이블의 department_code를 참조한다.

5 : department2 테이블의 department_code가 바뀌면 belong도 바뀌고, 삭제가 되면 함께 삭제 된다.

우선 위 코드에서 첫번 째 쿼리(1~5)를 실행시키면 외래키가 설정된다. (문제가 있다면 모든 테이블을 제거 후 실행하실 바란다.)

위 쿼리가 실행되면 student 테이블에 참조키가 표시되는 것을 확인할 수 있다. belong의 key 가 Mul

두번 째 쿼리(7~9)를 실행하면 department2 테이블의 'IME' 값이 'CCE'로 바뀌며, student 테이블의 belong 또한 함께 바뀌는 것을 확인할 수 있다.

세 번 째 쿼리(14~15)를 실행하자 위 student 테이블에는 16개의 데이터가 있었는데, belong이 'MSE'인 값들이 모두 사라져 14가 된다.

참조의 무결성

 

department2 테이블에는 IDE, CSE, CCE, ESE, MSE 이렇게 5가지 값이 있다. 그리고 student 테이블의 belong값도 마찬가지로 5개의 값을 가지고 있다.

만약 department2의 테이블에서 MSE가 제거가 되었고, student(belong)에는 여전히 5개의 값을 가지고 있는 상태에서 department2(department_code)를 외래키로 지정할 때 오류가 발생한다. 그 이유는 참조하는 테이블에 없는 값을 가지고 있기 때문이다.

 외래키로 설정한 값이 참조하는 테이블의 속성 없으면 안된다는 규칙이다.

외래키를 제거 하고 싶다면 네 번째 쿼리(19~21)을 실행시킨다. 외래 키 제거를 두가지로 하는데, DROP FOREIGN KEY는 외래키 제약조건을 제거하지만

DESC 또는 SHOW CREATE TABLE로 볼 때 여전히 키로서 명시되어 있다. 이 이유는 잘 모르겠다.

그래서 DROP KEY로 키를 한 번 더 제거해야 한다.

2. 1NF 실습

toy 테이블은 현재 열에 여러값을 가지고 있기 때문에 제 1 정규화가 되어 있지 않다.

1NF의 규칙

규칙 1 : 원자적 데이터로 구성된 열은 그 열에 같은 타입의 데이터를 여러개 가질 수 없다.

규칙 2 : 원자적 데이터로 구성된 테이블은 같은 타입의 데이터를 여러개 가질 수 없다.

일단, 임시로 toy 테이블에 color1, color2, color3 열을 추가하여 각 데이터를 추가해보자.(이렇게 나누는 것 또한 규칙 2에 위배된다.)

그리고 새로 color 테이블을 생성하여 색상 정보를 가지는 테이블을 만들고,  toy와 color의 관계를 나타내는 toy_color 테이블을 만들 것이다.

첫 번째 쿼리(1~4)를 실행하면 

color1, color2, color3 열이 생긴다. colors의 값을 ','를 기준으로 잘라서 넣도록 하겠다.

두 번째 쿼리(6~7)와 세 번째 쿼리를 실행(11~12)하면 아래와 같이 값이 복사 된다.

모든 쿼리를 실행하면 최종적으로 아래와 같은 결과가 된다.

이제 color 테이블을 만들어 모든 색상을 저장하자. 이 때 중복된 값으로 저장되지 않게 조심하자.

첫 쿼리(1~6)는 color 테이블을 만든다.

두 번째 쿼리(8~10)은 color1의 값을 중복되지 않게 color 테이블의 name에 저장한다.

세 번째 쿼리(12~15)은 color2의 값 중 color(name)에 없는 값을 새롭게 추가 한다.

네 번째 쿼리(17~20) color3값을 위와 반복,

color_id = 8 인 값을 보면 의미없는 공백 값이 들어가 있다. toy 테이블에도 의미 없는 공백값을 가지고 있다. 이러한 의미 없는 값을 없앨 수 있도록

각자 생각해보면 좋겠다. 지금은 쿼리가 복잡하지 않게 하기 위해 이 문제는 무시하도록 하겠다.

이제 color 테이블을 만들었고, toy가 어떤 색상을 갖는지 알기 위해 toy 와 color를 매핑하는 테이블을 만들겠다.

첫 번째 쿼리(1~14)가 조금 길지만 외래 키 제약 조건하는 것은 이미 다뤘다. 5 번째 줄에 toy_id 와 color_id를 복합키로 만드는 방법이 처음 나왔다.

32 : toy_id 가 7인 장난감이 가지고 있는 색상을 검색한다. 결과는 4, 5, 6으로 red, pink, blue를 가진다.

지금까지 제 1정규형으로 만드는 것을 진행했다. 지금까지 다룬 내용은 실무에서도 유용하게 사용할 수 있을 것이라 생각된다.

다음 시간에는 JOIN에 대해 공부할 예정이다.