IDの自動生成と削除

解決

どうすれば 2010-01-23 22:50:17 No: 37207 IP: 192.*.*.*

以前お伺いしたIDに関する追加質問です。

オラクルを使っていて、IDを自動生成するいい方法を探しています。今、作成している方法は、

1: query1をつかって、sql.add('select max(ID) from table')で現在登録されている最大のID番号を持ってくる。ちなみに、IDの型はintegerです。

2:別query2をつかって、sql.add('insert .. into (valueID)');
prepare;
params[0].asinteger := ここにquery1で取得したmaxIDの値+1をいれたいとおもっているのですが、呼び出し方がわからない状態です。

また、登録前に、現在すでに登録している最小IDを探し出し、０よりも大きく最小ID値よりも小さい数字が存在した時は、そのIDを呼び出して、insert
したいとおもっています。

すみませんが、ご教授お願いいたします。

HOta 2010-01-24 07:42:18 No: 37208 IP: 192.*.*.*

前の質問で、Mr.XRAYさんが、
>OracleにはCREATE SEQUENCEというのがあります．
と書いていますが、調べましたか?
Oracle側で生成してくれますよ。

どうすれば 2010-01-24 11:09:02 No: 37209 IP: 192.*.*.*

INSERT ... INTO TABLE VALUE(ID.NEXTVAL,....)とすると、IDの値がどんどん大きくなってしまい、最終的にはこれ以上生成できないという状態になると考えられます。仮に、IDの値が999までしか入らないとして、すでに999まで使用している状態だが、20〜30番の間はデータを削除したので使用できる場合、使えるIDを探して20〜30のIDでデータを登録する方法を探しています（30番まで達したら、すでに存在している31へ行くのではなく、もうデータが登録できないと判断する）。

どうすれば 2010-01-24 12:47:04 No: 37210 IP: 192.*.*.*

★追加★CREATE SEQUENCEについての追加質問があります。NEXTVALを使用したい場合には、SQLでCREATE SEQUENCEを実行しないといけないと思うのですが、どのテーブルにCREATE SEQUENCEを使うのか指定することができません。

いろいろと説明を読みましたが、その部分に疑問をがあります。これは、ただテーブルの指定せずSQL上で実行すればいいのですか

igy 2010-01-24 14:40:56 No: 37211 IP: 192.*.*.*

>20〜30番の間はデータを削除したので使用できる場合、使えるIDを探して

は、
SELECT ID FROM テーブル名 ORDER BY ID
で、IDの値を１つずつ確認し、欠けている（削除している）ID
を探していくのは、いかがですか？

中級SQL 2010-01-25 00:44:15 No: 37212 IP: 192.*.*.*

下のQueryで空きIDの内の最小のもの(gapID)が得られるはずです。
(使ったことあります)
select min(a.ID + 1) as gapID
from (select ID from TableA) a
where not exists
(select * from TableA b where b.ID = a.ID + 1);
gapIDがMaxID+1なら空きがないことになります。
しかし、IDに999などの上限を設ける理由があるのですか？
IDが(long)Integerなら21億まで使えるのに

中級SQL 2010-01-25 01:25:29 No: 37213 IP: 192.*.*.*

すみません、前発言のSQLは、
IDが１から振られていて、1,2,3とIDが削除されていた場合、
1を抽出できません。
(ゴメン m(__)m)
DB Engineにもよりますが、IF とか WHEN などが使えれば
MIN(ID)が1かどうかの判定と前発言のSQLを組み合わせた
１本のSQLでいけるのではないでしょうか

HOta 2010-01-25 07:31:44 No: 37214 IP: 192.*.*.*

OracleのCREATE SEQUENCEは、初期値・最小値・最大値を設定できます。最大値を超した場合は、初期値にリセットされます。

どうすれば 2010-01-25 22:30:23 No: 37215 IP: 192.*.*.*

私の環境だと create sequeceが使えないみたいです。データベースの管理者に言われました。この場合は、max(ID) + 1をselect文で使わないといけないと思うのですが、
変数にi := qry.sql.add('select max(ID) from table);としても、max値をとってきません（１をとってきます）。
select min(a.ID + 1) as gapID
from (select ID from TableA) a
where not exists
(select * from TableA b where b.ID = a.ID + 1);も試したのですが、同様の結果になりました。

igy 2010-01-25 23:01:58 No: 37216 IP: 192.*.*.*

>変数にi := qry.sql.add('select max(ID) from table);としても、max値をとってきません（１をとってきます）。

Add メソッドの戻り値ではなく、
そのSQL文を実行してみたらどうなりますか？

どうすれば 2010-01-25 23:34:32 No: 37217 IP: 192.*.*.*

SQL文だと、max値をとってくることができました。

中級SQL 2010-01-26 01:32:05 No: 37218 IP: 192.*.*.*

欠番IDの再利用にこだわるのであれば、ID取得関数を作っては？
下記でどうですか？
//IDを取得する関数
function GetNextID: Integer;
begin
  with query1 do begin
    SQL.clear;
    SQL.Add('select min(ID) as A from TableA');
    Open;
  //min(ID)が1の時は欠番を探す(欠番がなければ(MaxID+1)を)
    if FieldByName('A').AsInteger = 1 then begin
      close;
      SQL.clear;
      SQL.Add('select min(a.ID + 1) as NextID');
      SQL.Add(' from (select ID from TableA) a');
      SQL.Add(' where not exists');
      SQL.Add(' (select * from TableA b where b.ID = a.ID + 1);');
      Result := FieldByName('NextID').AsInteger;
      close;
    end else begin  //Min(ID)>1のときは1〜(MinID-1)が欠番となっているので1を再利用
      close;
      Result := 1;
    end;
  end;
end;

//メイン側
var
  ID: Integer;
begin
  ID := GetNextId;
  if ID > 999 then begin
    ShowMessage('満杯で〜す！');
    Exit;
  end;
  with query2 do begin
    SQL.clear;
    SQL.Add('Insert Into TableA (ID, Data1, Data2, ...)'
    SQL.Add(' Values (:ID, :Data1, :Data2, ...');
     :
     :
   end;
end;

中級SQL 2010-01-26 01:33:55 No: 37219 IP: 192.*.*.*

訂正(追加)
>      SQL.Add(' (select * from TableA b where b.ID = a.ID + 1);');
       Opne;     //<---抜けてました
>      Result := FieldByName('NextID').AsInteger;

中級SQL 2010-01-26 01:52:58 No: 37220 IP: 192.*.*.*

訂正の訂正が必要ですね
  追加行の"Open"のつづりがまちがってます。(トホホ…)

おまでです、
SQLでCase文を使えば1本のSQLで行けそうです。
下記SQLはFireBird 2.1で通ります。Oracleでも多分OKと思います。
function GetNextID: Integer;
begin
  with query1 do begin
    SQL.clear;
    SQL.Add('select case when min(ID) = 1 then');
    SQL.Add(' (select min(a.ID + 1) from (select ID from TableA) a');
    SQL.Add(' where not exists (select * from TableA b where b.ID = a.ID + 1))';
    SQL.Add(' else 1 end as NEXTID from TableA;');
    SQL.Open;
    Result := FieldByName('NEXTID').AsInteger;
    Close;
  end;
end;
簡単なサンプルテーブルを作って
  ID=1を欠番にした時      １が得られる
  ID=1以外を欠番にした時  欠番の内の最小値が得られる
  IDに欠番がない時        最大値+１が得られる
の３通りで確認してみてください。

どうすれば 2010-01-28 21:37:28 No: 37221 IP: 192.*.*.*

ありがとうございます。ちなみに、プライマリキーがかかっているIDすべてを一度リセットして、DelphiのSQL文を使ってIDを再度割り振る方法をご存知ですか？

中級SQL 2010-01-29 01:26:55 No: 37222 IP: 192.*.*.*

"一度リセットして"の意図が、
3,4,5, 10,11,12, 18,19,20 のように歯抜け状態のIDを元に
1,2,3, 4, 5, 6, 7, 8, 9 のように同じIDフィールドに対してSQL一発で
連番にしたいということであれば、無理だと思います。
もし、出来るのならそのDB EngineとSQL文を私も知りたいです。

新フィールド"ID2"を追加する。
歯抜け"ID"の順に"ID2"にSQLで連番を振る <== これはSQLで可能です
"ID"を削除する
"ID2"を"ID"に名称変更し"Unique"設定をする
と言う手順を踏むのが無難な方法ではないでしょうか。

どうすれば 2010-01-29 06:07:35 No: 37223 IP: 192.*.*.*

いろいろな情報どうもありがとうございました。