[B급 프로그래머] SQL에서 UNIQUE와 NULL 제약 조건

NoSQL과 같은 최신(응?) 기술이 판을 치는 상황에서 갑자기 구닥다리 SQL을 꺼내 설명하려드니 독자 여러분들께서는 당황 아니 황당하다는 생각이 들지도 모르겠다. 하지만 프로그램을 작성하다보면 아주 기초적인 내용임에도 불구하고 알쏭 달쏭한 경우가 있기 마련이고, 궁금증이 생겼으면 뿌리부터 뽑아버려야 나중에 고생을 덜한다. 오늘 다룰 내용은 UNIQUE와 NULL이다. MySQL이라는 특정 데이터베이스를 기준으로 실험을 할 계획이니 혹시 진짜 여기 나오는 내용을 따라하고 싶은 독자라면 mysql 클라이언트를 하나 띄워놓기 바란다.

자 그러면 시작해보자. 가장 먼저 테이블을 생성하는 과정에서 PRIMARY 키에 대해 NULL 제약 조건을 허용하면 어떤 일이 벌어질까? PRIMARY인데 NULL이 가능한가? 원칙적으로는 모순되는 조건이다.

mysql> create table person (name varchar(64) null, age int, primary key(name)) ENGINE=InnoDB;

다음 명령 결과를 보면 알겠지만, MySQL은 내부적으로 (명시적인) null 지정을 가볍게 무시한다. PRIMARY 키로 지정한 name 필드의 Null을 보면 NO로 설정되어 있다.

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(64) | NO   | PRI |         |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

자 그렇다면 난이도를 높여 약간 어려운 질문을 해보자. 위에서 정의한 person 테이블에 insert할 때 다음과 같이 name 값을 주지 않으면 어떤 일이 벌어질까? NOT NULL 조건이라는 사실을 염두에 두고 생각하면 오류가 발생할 것 같기도 하다.

mysql> insert into person (age) values (11);

하지만, 흥미롭게도 결과는 오류가 발생하지 않는다. select로 확인해보면 다음과 같다.

mysql> select * from person;
+------+------+
| name | age  |
+------+------+
|      |   11 |
+------+------+

이런! 화들짝 놀라는 분들도 분명히 계실 것이다. NOT NULL로 지정한 name 필드가 비어 있어! 물론 놀랄 필요가 전혀 없다. 다음 실행 예를 보면 이해가 갈테니까.

mysql> select * from person where name = NULL;
Empty set (0.00 sec)
mysql> select * from person where name = "";
+------+------+
| name | age  |
+------+------+
|      |   11 |
+------+------+
1 row in set (0.00 sec)

예전 수학 시험 문제 중 답이 없는 문제가 하나 있었는데, 답안을 쓰지 않은 학생이 선생님을 찾아가 "답이 없어 안 썼어요!"라 말했다가 혼난 기억이 새롭다. 선생님의 대답은 "답이 없으면 "답 없음"이라 써야지 안 그러면 자네가 모르는지 아니면 정말 답이 없는지 내가 어떻게 구분하나?"였다. NULL은 비어있는 상태를 의미하는 반면 ""는 빈값(즉 비어있는 문자열)을 의미한다(여기에 대해 나중에 블로그 글을 하나 더 올려드릴 계획이다). 여기서 Default가 NULL이 아니라(만일 NULL이라면 앞서 지정한 NOT NULL 조건에 위배된다) ""이라는 사실을 이해하면 이 모든 궁금증이 해소될 것이다.

NULL, NOT NULL로 충분히 장난을 쳤으니 이제 UNIQUE로 넘어가보자. 아까 테이블 그 상태 그대로 다시 다음과 같이 insert 문을 수행해보자. 어떤 결과가 나올까?

mysql> insert into person (age) values (12);

지금쯤이면 독자 여러분들도 속지 않으리라 믿는다. 다음 select 문 결과를 예측하고 있어야 한다.

mysql> insert into person (age) values (12);
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'

간단한 결론: PRIMARY는 NOT NULL이자 UNIQUE 속성이 자동으로 붙는다.

이제 UNIQUE에 집중해보자. 만일 PRIMARY가 아니라 다른 복합 색인을 생성하면 어떻게 될까? person 테이블을 조금 변경해보자.

mysql> drop table person;
mysql> create table person (name varchar(64), age int, money int, primary key(name), index myindex (age ASC, money ASC)) ENGINE=InnoDB;

myindex라는 age와 money를 포함하는 색인을 명시적으로 생성했다. 이제 다음과 같이 age, money 쌍이 동일한 값을 두번 insert 한다. scott/tiger를 보고 웃으면 당신은 옛날 사람이다. ㅋㅋㅋ

mysql> insert into person values ('scott', 100, 100);
mysql> insert into person values ('tiger', 100, 100);

앞서 PRIMARY와는 달리 중복되었다고 투덜거리지 않고 자료가 입력된다. 그러면 중복을 막으려면? myindex에 UNIQUE 조건을 걸어보자.

mysql> drop table person;
mysql> create table person (name varchar(64), age int, money int, primary key(name), unique index myindex (age ASC, money ASC)) ENGINE=InnoDB;

다시 한번 중복해 자료를 입력한다.

mysql> insert into person values ('scott', 100, 100);
mysql> insert into person values ('tiger', 100, 100);
ERROR 1062 (23000): Duplicate entry '100-100' for key 'myindex'

당연히 중복 오류가 발생한다. 자 그렇다면... age와 money를 둘 다 넣지 않으면 어떻게 될까?

mysql> delete from person;
mysql> insert into person (name) values ('scott');
mysql> insert into person (name) values ('tiger');

NULL-NULL이 중복될 경우 UNIQUE 조건을 위배하는지를 묻는 질문인데 주사위를 던져 정상/오류를 답하고 싶을지도 모르겠다. 중복되었다고 투덜거리지 않고 자료가 입력된다!

mysql> select * from person;
+-------+------+-------+
| name  | age  | money |
+-------+------+-------+
| scott | NULL |  NULL |
| tiger | NULL |  NULL |
+-------+------+-------+

앞서 설명했지만 NULL은 값이 아니라 비어있는 상태를 의미하므로 중복되었다고 볼 수 없다. 이미 예상하고 있겠지만, 단일 필드를 대상으로 unique를 지정해도 마찬가지로 NULL 중복(?)이 일어나지 않는다.

독자 여러분의 총정리를 위해 마지막으로 정말 쉬운 문제를 내며 이 글을 마무리하겠다. 만일 PRIMARY가 앞서 살펴본 단일키가 아니라 복합키로 구성되어 있을 경우 1) 중복을 허용하지 않게 하려면 UNIQUE를 붙여야 할까 붙이지 않아도 자동으로 될까? 2) 만일 PRIMARY 복합키는 중복되지 않더라도 복합키를 구성하는 개별 키가 중복되면(주의 기본값!) 어떤 일이 벌어질까? 다음 테이블로 직접 테스트해보시라!

mysql> drop table person;
mysql> create table person (name varchar(64), age int, money int, house int, primary key(name, house), unique index myindex (age ASC, money ASC)) ENGINE=InnoDB;

EOB