Sql-Statement für kleinste nicht vorhanden Nummer

[barnti]

Hallo Alle,

ich versuche für eine Tabelle eine ID zu generieren. Dazu möchte ich die kleinste noch nicht vorhandene ID für den neuen Eintrag vergeben. Wie kann ich das effizient in ein SQL-Statement umsetzen?

Danke für eure Ideen, Gruß,

Barnti

[Hansa]

Üblicherweise macht man das mit einem Before-Insert-Trigger.

[barnti]

Aha,

kannst Du das etwas ausführlicher erläutern. So kannn ich mit Deiner Antwort leider nicht viel anfangen...

Gruß,

Barnti

[Hansa]

Das war mir schon klar. 8) Aber das ist zu kompliziert, um es in einem Satz zu erklären, deshalb hab ich Dir das Stichwort "Trigger" gegeben. Das ist eine DB-Funktion, die praktisch im Hintergrund nur darauf wartet, etwas tun zu dürfen. In dem Zusammenhang kommen auch Generatoren ins Spiel. Der Trigger setzt den Generatorwert (das ist die gewünschte ID) um eins hoch. Hört sich kompliziert an, so schlimm ists aber gar nicht. Ich empfehle Dir hierzu den Kurs von Paul Jr., frage mich aber nicht wo der ist. Mußt suchen.

[Tanadirian]

Du kannst es auch umständlich machen! Mit einer Abfrage und einer Schleife :wink:

SQL-Code:

select ID as ID from Table1

damit bekommst du die Werte der ID-Spalte schon geordnet (aufsteigend!?)

Delphi-Quellcode:

i:=1;
repeat
if i <>query1.fieldbyname('ID').asinteger then break;
(*Mit dem "<>" bin ich mir nicht ganz sicher kann auch nur "<" sein*) 
i:=i+1;
query1.next;
until i=query1.recordcount+1;//damit die Schleife auch mal endet
//jetzt ist i der kleinste ID-Wert der fehlt

Das Beispiel geht natürlich davon aus das deine ID's bei 1 anfangen!

[r_kerber]

@Hansa: Nicht jede Datenbank kennt Generatoren!
@Tanadirian: Und was machst Du, wenn während Du suchst, Datensätze gelöscht oder hinzugefügt werden werden?

[Tanadirian]

@r_kerber: Man kann in diesem Fall doch am Ende die Recordcounts von Query1 und Table1 vergleichen und wenn sie nicht übereinstimmen muß man halt das Ganze nochmal machen. (Ich hoffe du meintest das, ansonsten versteh ich deine Frage nicht :oops: )

[r_kerber]

Was hältst Du dann von einer Tabelle, in der die ID geführt wird. Im Insert-Trigger wird dann transaktionssicher die nächste ID ermitteln. Dies im Client-Programm machen zu wollen, halte ich für ungünstig!

[Tanadirian]

So fit bin ich leider auch noch nicht in SQL :(

[r_kerber]

Alternativ kann mann auch einen Generator (Interbase) oder Sequence (Oracle) verwenden oder für ID einen AutoInc-Datentyp (z.B. Seriell in Informix) verwenden! Damit bist Du dann aber abhängig von der Datenbank!

[Leuselator]

ist zwar nicht die "reine Lehre" aber vielleicht ein brauchbarer Ansatz, falls Deine DB keine Trigger, wohl aber Subselects unterstützt:

SQL-Code:

Insert into TabellenName
(ID, MeinZahlWert, MeinCharWert)
select max(ID)+1,
       4711 as MeinZahlwert,
       'NurEinBeispiel' as MeinCharWert
  from Tabellenname

Gruß

[Wormid]

Wenn Du es mit simplem sql lösen willst, dann würde ich vorher mal folgendes Statement absetzen:

SQL-Code:

SELECT MAX(id) FROM name_der_tabelle

Damit wird Dir dann die bisher größte vergebene id ausgegeben... Da mal fix ne 1 hinzuaddiert, und schon bist Du fertig.

Allerdings haben so gut wie alle Datenbanken einen Typen wie "autoincrement" oder sowas, was spricht denn gegen den? Warum willst Du das manuell machen?

Gruß

Wormid

[Sharky]

Hai barnti,

überlege doch mal ob es nicht einfacher ist anstelle einer nummerichen ID eine GUID zu verwenden.

[Tanadirian]

Ich hab ihn so verstanden das er die kleinste frei ID sucht! :gruebel:
Nicht einfach die nächstgrößte.

[r_kerber]

Zitat:

Zitat von Sharky

überlege doch mal ob es nicht einfacher ist anstelle einer nummerichen ID eine GUID zu verwenden.

Als Primärschlüssel eine Zeichenkette?....

Zitat:

Zitat von Tanadirian

Ich hab ihn so verstanden das er die kleinste frei ID sucht!

Stimmt. Wer lesen kann ist klar im Vorteil. :oops: :oops: :oops:

[Wormid]

Etwas suchen, was NICHT da ist, ist aber gar nicht so einfach... Ich wüsste nicht, ob das mit einem SQL-Statement überhaupt geht. Ich denke, da müsste man dann entweder zusätzlich eine Tabelle mit freien IDs pflegen, oder irgendwie Schleifen-Logic in die Application einbauen, die in einem Loop die erste freie ID sucht. (Achtung... langsaaaaaam)

Ich immer eh davon abraten, einmal gelöschte IDs später wieder zu vergeben. Sicher ist sicher.


Gruß

Wormid

[r_kerber]

Zitat:

Zitat von Wormid

Ich immer eh davon abraten, einmal gelöschte IDs später wieder zu vergeben. Sicher ist sicher.

Dem kann ich mich nur anschließen!

[Sharky]

Zitat:

Zitat von r_kerber

Zitat:

Zitat von Sharky

überlege doch mal ob es nicht einfacher ist anstelle einer nummerichen ID eine GUID zu verwenden.

Als Primärschlüssel eine Zeichenkette?....

Warum nicht? Meinst Du es geht zu sehr auf die Geschwindigkeit?
Mir ist noch nichts aufgefallen (allerdings ist die Datenbank auch nicht riesen groß)

[woki]

Hallo barnti,

ich möchte vorsichtshalber nochmal etwas deutlicher warnen: Deine Lösungsidee, für die du eine Implementation suchst, löst nicht dein Problem, also liefert nicht das, was man braucht um einen Datensatz jetzt und in alle Zukunft eindeutig zu identifieren. Wenn Du auf einer ID, die einfach die kleinste zur Zeit nicht verwendete Zahl einer z.b. Integer Spalte ist, z.B. Relationen aufbaust, wird das gnadenlos knallen.

Erstens muß Du sicher stellen, das deine ID eindeutig ist (in einer Multiuser-Umgebung ist das nicht so einfach wie es sich anhört), zweitens mußt du sicher stellen, daß eine schon mal verwendete ID nie wieder verwendet wird, auch wenn der Datensatz gelöscht wurde.

Ich kenne eigentlich keine Datenbank, die diesen Namen verdient, und die keinen Mechanismus dafür zur Verfügung stellt, nämlich entweder Autoincrementfelder oder eine Generator (auch wenn der dann eventuell anders heißt, z.B. sequence (Oracle siehe oben)).

Grüsse
Woki

[woki]

Zitat:

Zitat von Sharky

Zitat:

Zitat von r_kerber

Zitat:

Zitat von Sharky

überlege doch mal ob es nicht einfacher ist anstelle einer nummerichen ID eine GUID zu verwenden.

Als Primärschlüssel eine Zeichenkette?....

Warum nicht? Meinst Du es geht zu sehr auf die Geschwindigkeit?
Mir ist noch nichts aufgefallen (allerdings ist die Datenbank auch nicht riesen groß)

Immer dann, wenn ein round trip zur Datenbank schmerzhaft oder unmöglich ist, bietet sich die Verwendung von Guids als Alternative zu buchungskreisen an, um eine eindutige ID zu erhalten, und hat da auch Verbreitung gefunden. Nachteil sind aber schon Performance und Datenmenge, das muß man im Einzellfall prüfen.

Grüsse
Woki

[NicoDE]

Möglicher Lösungsansatz: im Statement eine Menge A mit allen ID und eine Menge B mit allen ID+1 generieren und so verbinden, dass als Ergebnismenge nur alle B zurückgeben werden, die nicht in A enthalten sind - der kleinste Wert ist die gesuchte 'freie' ID.

Das Problem dabei ist, das dies damals (vor ca. vier Jahren) nur mit Oracle in _einem_ SQL-Statement möglich war, und somit performant auf dem Server optimiert werden konnte.

Ansonsten lies die Hinweise von woki, sonst wirst Du früher oder später Probleme bekommen...


Gruß Nico

ps: ist zu lange her, als dass ich das o.g. Statement noch zusammenbekommen würde (hat meinen Kollegen ein paar Tage gekostet darauf zu kommen :))

[Jelly]

Hier wäre eine Lösung

Code:

select min(ID+1) as FreieID from Tabelle 
where not (ID+1 in (Select ID from Tabelle))

Voraussetzung, die Datenbankengine unterstützt die Syntax. Bei MySQL gehts glaub ich nicht. Allerdings soll die neueste Version jetzt Subqueries unterstützen, hab die aber nicht installiert. Kann ja mal einer ausprobieren und hier berichten.

Gruß,
Tom

[barnti]

Hallo Leute,

das ist alles ganz ok nur kommen bei mir noch einige randbedingungen hinzu.

Ich löse das jetzt folgendermassen:

Es gibt zwei Tabellen:

Eine Stück-Tabelle, also die mit der gearbeitet wird

und eine Pool Tabelle, in der sich alle freien IDs befinden.

Wird nun in der Stücktabelle ein Datensatz angelegt so wird inkrementell geschaut, ob die ID bereits vorhanden Ist. Etwa so

Delphi-Quellcode:

Fuction IDErzeugen(ID: String): String;

begin
  if ID < 1000 then
     begin
       if Query.locate(nummer) then
          result:= nummer;
       else
          result:= IDErzeugen(Nummer+1);
     end
  else
     begin 
       result:= FindeIDInPool(nummer);     
     end
end;


Function FindeIDInPool(Nummer: String): String;
begin
  try
    QueryPool.locate
  except
    result:= nummer+1;
   end;
end;

So erzeuge ich eine ID die möglichst unter 1000 liegt. Werte die in der StückTabelle entfernt werden, werden mit einem Ende Datum versehen, das ein Teil des Schlüssels ist....

Wie gesagt: sind kompliziertere Bedingungen....

Danke für die rege Beteiligung, Gruß,

Barnti

[barnti]

Hallo Leute,

das ist alles ganz ok nur kommen bei mir noch einige randbedingungen hinzu.

Ich löse das jetzt folgendermassen:

Es gibt zwei Tabellen:

Eine Stück-Tabelle, also die mit der gearbeitet wird

und eine Pool Tabelle, in der sich alle freien IDs befinden.

Wird nun in der Stücktabelle ein Datensatz angelegt so wird inkrementell geschaut, ob die ID bereits vorhanden Ist. Etwa so

Delphi-Quellcode:

Fuction IDErzeugen(ID: String): String;

begin
  if ID < 1000 then
     begin
       if Query.locate(nummer) then
          result:= nummer;
       else
          result:= IDErzeugen(Nummer+1);
     end
  else
     begin 
       result:= FindeIDInPool(nummer);     
     end
end;


Function FindeIDInPool(Nummer: String): String;
begin
  try
    QueryPool.locate
  except
    result:= nummer+1;
   end;
end;

Da kommt aufgrund der Rahmenbed. noch einiges hinzu...

Danke für die rege Beteiligung,

Barnti

[NicoDE]

Zitat:

Zitat von Jelly

Hier wäre eine Lösung [...]

Ich hatte vergessen zu erwähnen, dass es bei diesem Ansatz einen Sonderfall gibt - wenn die Tabelle leer ist (wir haben damals ID=0 -Datensätze beim Erzeugen der Tabelle angelegt, da wir diese ohnehin brauchten).


Gruß Nico

ps: um die Query-Performance zu optimieren, kann ich nur empfehlen, die jeweiligen Trace-Möglichkeiten zu nutzen (im Falle von Oracle z.B. EXPLAIN PLAN).

[Hansa]

@barnti: merkst Du nicht, daß da keine Angabe der Datenbank vorhanden ist ?

[barnti]

@Hansa: Wie lautet Deine Frage?

Zitat:

merkst Du nicht, daß da keine Angabe der Datenbank vorhanden ist ?

Zitat:

da

bedeutet?

und

Zitat:

Angabe der DB

heißt?

Bitte drück Dich doch etwas klarer aus...

Gruß,

Barnti

[Hansa]

Zitat:

Zitat von barnti

Bitte drück Dich doch etwas klarer aus...

Da du das nicht mal verstanden hast, sehe ich, daß es keinen Sinn hat, Dir den Trigger zu erklären, sofern das bei deiner DB überhaupt geht.

[barnti]

@Hansa danke für Dein Verständnis...

Gruß,

Barnti