23년 11월 2일(목요일) - 24번째 TIL

문제

지급된 강의를 듣는 중, 왼쪽과 같이 MAJOR - STUDENT 테이블 간의 참조 화살표가 생겨야 하는데 오른쪽처럼 생기지 않는 문제가 있었다.

CREATE TABLE IF NOT EXISTS MAJOR
(	
	major_code varchar(100) primary key comment '주특기코드', 
	major_name varchar(100) not null comment '주특기명',
	tutor_name varchar(100) not null comment '튜터'
);

CREATE TABLE IF NOT EXISTS STUDENT
(
	student_code varchar(100) primary key comment '수강생코드', 
	name varchar(100) not null comment '이름',
	birth varchar(8) null comment '생년월일',
	gender varchar(1) not null comment '성별',
	phone varchar(11) null comment '전화번호',
	major_code varchar(100) not null comment '주특기코드',
	foreign key(major_code) references major(major_code)
);

MAJOR 와 STUDENT 테이블 간의 생성 SQL은 위와 같았다. 

 

이미 삽입한 외래 키 수정하기

일단 이미 입력을 해버린 상황이었기 때문에, 외래 키 부분을 수정하여 고치기는 했다. 

인텔리제이의 오른쪽 데이터베이스 탭에서 STUDENT 테이블의 외래키 수정 버튼을 누른 뒤, 

빨간 네모 부분을 MAJOR 테이블로 선택해주고 확인을 누르고 다이어그램 새로고침을 해주니 화살표가 생겼다. 

 

하지만 보다 근본적인 해결책과 예방책이 필요하다고 생각했고, 몇 가지 더 시도해봤다. 

 

외래 키 제대로 삽입하기

이미 입력한 외래 키를 수정하는 거 말고, 입력을 잘 하고 싶다면 아래 SQL을 실행하면 된다. 

CREATE TABLE IF NOT EXISTS STUDENT
(
	student_code varchar(100) primary key comment '수강생코드', 
	name varchar(100) not null comment '이름',
	birth varchar(8) null comment '생년월일',
	gender varchar(1) not null comment '성별',
	phone varchar(11) null comment '전화번호',
	major_code varchar(100) not null comment '주특기코드',
	foreign key(major_code) references MAJOR(major_code)
);

 

아까 SQL 과의 차이점은 마지막 줄 끝의 major 가 MAJOR 로 대문자로 바뀌었다. 

이렇게 대문자로 수정 후 실행해주니 다이어그램의 참조방향도 표시가 잘 되었다. 

 

 

근데, MySQL의 문자열은 대소문자를 구분하지 않는 걸로 알고 있는데, 왜 대소문자에 따라 달라지지? 

 

이런 의문으로 시작되어 조금 더 알아보기 시작했다. 

 

원인 해결

원인은 다음과 같았다. MySQL은 일단 문자열은 대소문자를 구분하지 않는 건 맞다. 구분하고 싶다면 BINARY 키워드를 이용하면 된다. BINARY에 대해서는 너무 벗어나는 거니 따로 알아보시고, 진짜 원인은 데이터베이스명과 테이블명은 대소문자를 구분할 수도, 안 할 수도 있다는 거다.

 

mysql> SHOW VARIABLES LIKE 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |
+------------------------+-------+
1 row in set (0.02 sec)

일단 mysql 명령에 위와 같이 입력해보면, lower_case_table_names 의 값이 나오는데, 이 값의 범위는 다음과 같다. 

 

lower_case_table_names 값	의미
0	CREATE TABLE이나 CREATE DATABASE 실행 시 디스크에 저장되는 테이블과 데이터베이스의 이름을 대소문자 구분하여 생성한다 SELECT 나 INSERT 사용 시에도 대소문자를 구분해서 사용해야한다. 대소문자를 구별하는 OS에서만 의미가 있고 Windows/Mac OS X 에는 적용되지 않는다
1	테이블과 DB 이름을 소문자로 생성하며 참조시에는 소문자로 변경하여 처리한다 대소문자 구분 X 기존에 대문자가 포함되어 생성한 테이블과 DB는 문제가 될 수 있다
2	CREATE TABLE이나 CREATE DATABASE 실행 시 디스크에 저장되는 테이블과 데이터베이스의 이름을 대소문자를 구분해서 생성한다 참조시에는 소문자로 변경한다 대소문자를 구분하지 않는 팡일 시스템을 가진 OS(Mac OS X)에서만 동작한다

 

윈도우OS의 기본값은 1, 리눅스 및 유닉스는 0, 맥은 2가 기본 값으로 되어있다. 그래서 위에서 나는 2가 나왔다. 

 

 

바꾸는 법은 OS마다 달라서 간단히만 설명하자면, 

[mysqld]

lower_case_table_names = 1

 

my.cnf 에 들어가서 위와같이 작성하여 정할 수 있다.