auto_incrementを設定する場合の制約

int型に設定可能なオプション、auto_incrementは確かに便利なのですが、これも色々使う上で制約がありますよ、というお話です。

mysql> create table test1(id int auto_increment, name varchar(20), index(id));
Query OK, 0 rows affected (0.09 sec)

mysql> create table test2(id int auto_increment, name varchar(20));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

auto_incrementは必ず索引が付与されている必要があります。

mysql> create table test3(id1 int, id2 int auto_increment, name varchar(20), index(id1,id2));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> create table test4(id1 int auto_increment, id2 int, name varchar(20), index(id1,id2));
Query OK, 0 rows affected (0.00 sec)

auto_incrementを複数列索引の対象とする場合、auto_incrementを設定した列が先頭に来る必要があります。

伝票番号、伝票明細番号みたいな形で伝票明細番号だけ自動採番でDB設定しようとするとハマることがあります。特に複数列索引の場合の制約は、複合主キーのときにひっかかってくるので注意。とはいえ、ER設計次第でいくらでも回避できる話ではありますけどね。