IDの欠番

質問

「ID」フィールドにIDENTITYを設定し、1つずつインクリメントして
レコードにIDを割り振っていますが、INSERT時に何らかのエラーで
失敗するとその際のIDが欠番となって抜けてしまいます。
これを防ぐ方法、または修正する（欠番になったIDを詰める）には
どうしたらよいでしょうか？

回答No.3

temtecomai2 · ベストアンサー

> 失敗時にIDENTITYを使ったフィールドの番号を戻すにはどうしたらよいでしょうか？

DB 任せの採番ですから仕方ないです。
嫌なら自前で採番するようにします。

> IDにはIDENTITYを使わないほうがいいのでしょうか。

この場合の "ID" をどう認識していらっしゃるのかわかりませんが、例えば伝票番号のように、使用者側の目に見える番号で、なおかつ(どんなときも)連番が要求される性格の番号なら IDENTITY を使わない方が良いですよ、という主張です。
人の目に見えてしまうから「欠番なんとかならないの？」という意見が出てしまうのです。

そういった番号ではなくて「単純に DB のシステム内で一意にするための番号」というのであれば IDENTITY を使う列をレコードの主キーにしてもかまわないと思います。

そこで、「人の目に見える番号」の部分をどうやって組み立てるかは、何の情報を管理する DB なのかによって違ってきます。これはビジネスルールの範囲です。
伝票番号であれば「正常に登録できたレコードにおいて連番が保証される番号」が採番できるようにする仕組みにしたり「政情に登録できたレコードにおいて、登録年の中で連番が保証される番号」が採番できるようにしたりとか。

お礼

自分で管理することで解決しました。
ありがとうございます！

回答No.4

corgi12kg

No.2です

ウソをついたようですね
ごめんなさい

お礼

ありがとうございます！

回答No.2

corgi12kg

「トランザクション管理」というの勉強しましょう

参考URL：

http://www.microsoft.com/japan/msdn/sqlserver/columns/webtech/webtech1.asp

補足

ありがとうございます。
トランザクションをためしてみたのですが、成功時にのみコミットするようにしたのですが、失敗した場合、やはりIDENTITYを設定したフィールドは番号が抜けてしまうようです。失敗時にIDENTITYを使ったフィールドの番号を戻すにはどうしたらよいでしょうか？

回答No.1

temtecomai2

そういう人の目に触れて、なおかつビジネスルール敵に連番を保証しなくちゃならない番号に IDENTITY の列を使わない方がいいですよ。
IDENTITY の列はあくまでも内部管理的な連番ってことで。

連番の振り方はいろいろあると思いますが、タイミング的には
INSERT や UPDATE のトリガーの中で採番する。
INSERT や UPDATE をストアドプロシージャで行うようにし、その中で採番する。
ただしロックをして重複を回避する。

番号の取り方は
最大値 + 1 を採用する。
番号管理テーブルを使う。
など。

DELETE して穴が開いた場合にどうするつもり？ って点も大きな問題。
それ以降のレコードを全部前に詰める。（リレーションでぶら下がっているレコードも全て詰める必要があるんで絶対にやりたくない方法）
INSERT 時に空き番号を探してそこへ登録する。（処理が重そう）
# というか、そんな重要な番号が動いちゃって構わないの？ という素朴な疑問。

補足

ありがとうございます。
人にDBのバックアップ＆リストアするとIDが連番に変わると聞いたので
（IDENTITYをセットしていると）、IDが抜けるとまずいと思い質問しました。

IDにはIDENTITYを使わないほうがいいのでしょうか。

>DELETE して穴が開いた場合にどうするつもり
今回データは蓄積してゆくので幸いDELETEはありません。