MySQL에서 두 개의 열로 이루어진 복합 기본 키(Primary Key)에 자동 증분(AUTO_INCREMENT)을 적용하려면 MyISAM 스토리지 엔진을 사용해야 합니다. InnoDB 엔진에서는 복합 키의 일부 열에 AUTO_INCREMENT를 지정할 수 없지만, MyISAM에서는 복합 기본 키의 마지막 열에 자동 증분을 설정하면 각 그룹별로 번호가 매겨집니다.
아래 예제를 통해 실제 동작 방식을 확인해 보겠습니다.
1. 두 개의 열을 기본 키로 하는 테이블 생성
먼저 Result와 StudentId 두 개의 열을 복합 기본 키로 지정하고, StudentId에 자동 증분을 적용한 테이블을 생성합니다.
mysql> create table TwoPrimaryKeyTableDemo
-> (
-> Result ENUM('First','Second','Third','Fail') not null,
-> StudentId int not null auto_increment,
-> StudentName varchar(200) not null,
-> Primary key(Result,StudentId)
-> )
-> ENGINE=MyISAM;
Query OK, 0 rows affected (0.20 sec)
여기서 핵심은 ENGINE=MyISAM 옵션입니다. 이 옵션이 있어야 복합 기본 키 내에서 자동 증분이 정상적으로 작동합니다.
2. 레코드 삽입하기
이제 테이블에 여러 개의 레코드를 삽입해 보겠습니다. 같은 학생 이름이라도 Result 값이 다르면 서로 다른 그룹으로 인식됩니다.
mysql> insert into TwoPrimaryKeyTableDemo(StudentName,Result) values('John','Fail');
Query OK, 1 row affected (0.42 sec)
mysql> insert into TwoPrimaryKeyTableDemo(StudentName,Result) values('Carol','First');
Query OK, 1 row affected (0.09 sec)
mysql> insert into TwoPrimaryKeyTableDemo(StudentName,Result) values('Smith','Third');
Query OK, 1 row affected (0.05 sec)
mysql> insert into TwoPrimaryKeyTableDemo(StudentName,Result) values('Johnson','Second');
Query OK, 1 row affected (0.03 sec)
mysql> insert into TwoPrimaryKeyTableDemo(StudentName,Result) values('Johnson','Third');
Query OK, 1 row affected (0.06 sec)
mysql> insert into TwoPrimaryKeyTableDemo(StudentName,Result) values('Carol','Second');
Query OK, 1 row affected (0.18 sec)
mysql> insert into TwoPrimaryKeyTableDemo(StudentName,Result) values('Carol','Fail');
Query OK, 1 row affected (0.05 sec)3. SELECT 문으로 결과 확인하기
ORDER BY 절을 사용해 저장된 데이터를 조회해 보겠습니다.
mysql> select * from TwoPrimaryKeyTableDemo order by StudentId, Result;
실행 결과는 다음과 같습니다.
+--------+-----------+-------------+
| Result | StudentId | StudentName |
+--------+-----------+-------------+
| First | 1 | Carol |
| Second | 1 | Johnson |
| Third | 1 | Smith |
| Fail | 1 | John |
| Second | 2 | Carol |
| Third | 2 | Johnson |
| Fail | 2 | Carol |
+--------+-----------+-------------+
7 rows in set (0.00 sec)
결과 분석
출력 결과를 보면 흥미로운 점을 발견할 수 있습니다. StudentId가 단순히 1부터 7까지 순차적으로 증가하지 않고, Result 값(그룹)별로 각각 1번부터 새롭게 시작되는 것을 확인할 수 있습니다.
First,Second,Third,Fail각 그룹마다StudentId가 독립적으로 1부터 증가합니다.- 예를 들어 'Carol'은
First그룹에서는 ID 1을,Second와Fail그룹에서는 ID 2를 가지게 됩니다.
이처럼 MyISAM 엔진의 특성을 활용하면 그룹별 자동 증분 번호를 손쉽게 구현할 수 있습니다. 다만 MyISAM은 트랜잭션과 외래 키를 지원하지 않으므로, 실제 프로젝트에 적용할 때는 이러한 제약 사항도 함께 고려하는 것이 좋습니다.