データベース設計 IDのインクリメント

Question

データベースでuserテーブルを作った時などIDをつけて、auto incrementにすると思います。

ですが、ユーザの削除などで使われないIDが出てくると思います。つまり1,2,3,4,5とユーザーがいて、3のユーザが削除されると、1,2,4,5となると思います。

この場合、4のユーザを3へ、5のユーザを4へなど変更しないのが普通なのでしょうか？

Answer 1

たとえは、この teratail で考えてみましょうか。

id : 10001
user: sumsum25

というデータが user テーブルにあるとします。（おそらくあるはずですね）

この質問の投稿を管理しているテーブル（とりあえず post としましょう）も当然あるはずです。
この投稿が、誰によって作られたものかがわかるように、user_id も持つはず。

id: 16297
user_id: 10001
title: データベース設計 IDのインクリメント
content: データベースでuserテーブルを作った時などIDをつけて、auto incrementにすると思います。...

この投稿をしたユーザー（ここではあなた）が退会したと言って、他の人が、10001番になったら、この投稿は誰がしたことになりますか？

おかしなことになってしまいますね。無理に整合性を保つとなれば、投稿だけじゃなくて、フォローのデータとか色々変更しなくてはいけなくなります。

この場合、4のユーザを3へ、5のユーザを4へなど変更しないのが普通なのでしょうか？

だから、変更しないのが普通というより、事実上「絶対してはいけない」ことなのです。

Answer 2

IDを外部キーに持っているテーブル全てに変更が必要になります。また、IDは連番である意味は全くないので変更するメリットがほぼありません。

しないのが普通というよりも、困難な上メリットがそんなにないためやらないという方が正確かもしれません。

Answer 3

データベース関連でやりがちなミスを集めたSQLアンチパターンという本がありますが、その21章に「シュードキー・ニートフリーク（疑似キー潔癖症）」という、ちょうどこの話題を取り上げたものがあります。

IDはデータベース管理用の値なので、詰める必然性もありませんし、それどころかRDBMSによっては、一意性を確保するために、「番号は生成したけど使われない」ということすら起きえます。

なお、どうしても飛びのない連番を振りたい場合は、IDとは別途で用意した方がいいでしょう。

Answer 4

主キーは変更しないのが普通です。

理由は、リレーションに関係なく、変更するメリットがないからです。

例えば、userテーブルのみしか扱わないアプリを想定してください。
別のアプリ参照は無い（どこかでリレーションする可能性がない）とします。

この状態で、ユーザーを削除した時に、
・歯抜けのIDを整列させて、
・インデックスを調整する
という対応（実装）する手間に必要性を感じますでしょうか？

まあ、それが質問なのでしょうが、必要になるケースは無いです。

ちなみに蛇足ですが、
userテーブルをauto incrementしている主キーでリレーションしてると、
あるタイミングでDelete-Insertの総入れ替えをした際にコケます。
DBの知識が高い人からすれば笑わってしまわれる運用かもですが、
現場では、こういう対応が起こります。

Answer 5

既に皆様がご回答なさっている通りで、不可能ではないかもしれないけれども、敢えて実施する事は希だと思います。こちら にも同類の回答が多数寄せられています。

身近な例で言えば、学生の出席番号（学籍番号）でも同じですよね？
誰かが転校して欠番が生じても詰めたりしません。
なぜかと言えば、過去の履歴の管理が面倒になるからです。

例えばA君が在籍していた1学期までの成績の記録と転向した２学期以降の記録で学籍番号がズレているとどうなるでしょうか･･･

ちなみに、話は逸れますが、auto increment を使用すること自体、注意が必要なようです。（ご参考）