Records mit einem Unique Key in Lücke aufrücken lassen

[QuickAndDirty]

Hallo wie kann ich Records in einem Feld mit Unique Key am Besten aufrücken lassen?
Wichtig dafür ist, dass keine SQL dialekt spezifischen fuktionen benutzt werden dürfen.
Z.b. Firebird hat "order by" in Updates aber leider hat MSSQL das nicht und irgendwie hat das keiner.

Angebnommen ich habe eine Tabelle

Code:

Gruppen

mit Feldern wie

Code:

ID INT, GruppenID INT, Position INT

und ein Unique Key für

Code:

GruppenID, Position

Wenn ich jetzt einen Record lösche möchte ich das die Records mit einer höheren Position in der Gruppe in die Lücke aufrücken.

Code:

UPDATE GRUPPEN 
SET POSITION = POSITION-1 
WHERE GRUPPENID = :GRUPPENID 
  AND POSITION > :POSITION

Ohne Unique Key geht das, aber mit Unique Key kann es zu Key Violations kommen je nach dem in welcher Reihenfolge die Updates durchgeführt werden.

Ich mache es jetzt quasi "von hand" in der richtigen reihen folge und es sieht aus wie viel zu viel Aufwand für ein einfaches löschen eines Datensatzes...

Gibt es einen besseren weg??????

Ich hab schon überlegt es wie folgt zu machen

Code:

UPDATE GRUPPEN 
SET POSITION = POSITION * (-1)
WHERE GRUPPENID = :GRUPPENID 
  AND POSITION > :POSITION;

UPDATE GRUPPEN 
SET POSITION = ( POSITION * (-1) ) -1 
WHERE GRUPPENID = :GRUPPENID 
  AND POSITION < 0;

also erstmal die werte auf negative werte setzen und sie dann auf den gewollten wert setzen um Duplikate zu vermeiden.

Vieleicht macht es auch sinn für den zweck die Tabelle und den Unique Key zu erweitern um ein feld

Code:

OLDPOSITION INT

Dann könnte man es so machen.

Code:

UPDATE GRUPPEN 
SET OLDPOSITION = POSITION
WHERE GRUPPENID = :GRUPPENID 
  AND POSITION > :POSITION;

UPDATE GRUPPEN 
SET POSITION = OLDPOSITION-1
WHERE GRUPPENID = :GRUPPENID 
  AND OLDPOSITION> :POSITION;

UPDATE GRUPPEN 
SET OLDPOSITION = NULL
WHERE GRUPPENID = :GRUPPENID;

Durch OLDPOSITION im Unique Key hätte man für das aufrücken etwas spielraum und mit dem NULL setzen von OLD position würde man die UNIQUE KEY Constraint auf Position wieder aktivieren...

Gibt's ein verfahren das quasi der Industrie-standard ist?

[hoika]

Hallo,
der "Industriestandard" ist, gar nichts zu tun.

Unique Keys sind künstliche Schlüssel.
Warum sollte man da nach dem Anlegen noch irgendwas ändern?

[Uwe Raabe]

Das funktioniert sowieso nur halbwegs konsistent, wenn es zusätzlich zu dem Unique-Key-Feld noch ein ID-Feld pro Datensatz gibt, welches sich niemals ändert. Wenn der Unique Key nicht nur zur Sortierung, sondern auch zur Identifikation verwendet wird, sollte er sich nie ändern.

[Delphi.Narium]

1. Eindeutige Schlüssel ändert man nicht.

2. In Firebird könnte es so aussehen:

SQL-Code:

select 'update gruppen set position = '||row_number() over ()||' where gruppenid = '||gruppenid||' and id = '||id as sql 
from gruppen where gruppenid = :GruppenID order by id

3. In MSSQL könnte es so aussehen:

SQL-Code:

select concat('update gruppen set position = ',row_number() over (),' where gruppenid = ',id,' and id = ',g.id,';') as sql
from gruppen where gruppenid = :Gruppenid order by id

Firebird baut Strings halt mit || zusammen, MSSQL mit Concat.

Damit FireBird auch mit Concat arbeiten kann, muss man sich eine passende Funktion bauen, die dann in die Scripte zum Datenbankaufbau mit rein muss:

SQL-Code:

SET TERM !!;
CREATE or alter function concat (
v1 varchar(2000), 
v2 varchar(2000) default '', 
v3 varchar(2000) default '',
v4 varchar(2000) default '',
v5 varchar(2000) default '',
v6 varchar(2000) default '',
v7 varchar(2000) default '',
v8 varchar(2000) default '',
v9 varchar(2000) default ''
)
returns varchar(2000)
AS
BEGIN
  return v1||v2||v3||v4||v5||v6||v7||v8||v9;
END!!
SET TERM ;!!

Nachteil: Concat von MSSQL kann bis zu 254 Parameter annehmen, die aber nicht angegeben werden müssen.
Ob diese "Wahlfreiheit" bei FireBird möglich ist, hab' ich nicht herausbekommen. Bei obiger Lösung müssen sie alle angegeben werden (deshalb hab' ich mal bei neun Parametern aufgehört).

Als Ergebnis käme ein SQL heraus, das dann unter MSSQL und FireBird funktionieren sollte:

SQL-Code:

select concat('update gruppen set position = ',row_number() over (),' where gruppenid = ',id,' and id = ',id,';','','') as sql 
from gruppen where gruppenid = :Gruppenid order by id

Die als Ergebnis erstellten Updatestatements müssten nun noch in 'ner Schleife, einem Executeblockkonstrukt, das für beide Datenbanken praktikabel ist, o. ä. ausgeführt werden.

Und nein, es ist keine gute Idee, einen eindeutigen Schlüssel zu ändern.

Wird hier der "Unique Key" nur als "datenbankseitige Hilfe" genutzt, um auf Fehler beim Setzen der Position hinzuweisen, mag es angehen. Es muss aber sichergestellte sein, dass niemand, wirklich niemand, die Kombination von GruppenID und Position in irgendeiner Form (ggfls., unter Umständen eventuell, vielleicht, auch nur ansatzweise) als Schlüssel verwendet. Flapsig formuliert: Der "Unique Key" darf alles sein, nur kein "Unique Key", sondern nur ein Konstrukt, das "irgendwie (datenbankseitig) geeignet ist", Positionsdubletten innerhalb einer GruppenID zu verhindern.

Funktioniert die Lösung?

Vielleicht.

Ist sie Industriestandard oder etwas, das diesem (wenn auch nur ansatzweise) ähnlich sein könnte?

Nein, sicherlich nicht.

Hilft sie bei der Behebung eines Problemes, das bisher unter "Missbrauch" eines "Unique Keys" gelöst werden konnte?

Eventuell.

PS: Funktion und SQL funktionieren unter FireBird. MSSQL hab' ich nicht, daher kann ich hier nur darauf hoffen, dass es funktionieren könnte. Als Idee sollte es aber ausreichen.

[jobo]

Es wurde ja schon sehr deutlich geschrieben, dass man das nicht macht. Und es gibt viele gute Gründe dafür.
Vielleicht noch zur Anti- Motivation:
Wenn der Grund, es dennoch zu tun, darin liegt, dass sonst irgendetwas in der Anwendung nicht funktioniert, dann sollte diese Stelle repariert werden, statt PK ID Lücken zu füllen.
Ein PK (und entsprechend FK) hat keine andere Funktion als die Schlüsselfunktion, also Eindeutigkeit. Er ist kein fachlicher Nachschlagewert, dient nicht zur Sortierung, ist nicht gleichschrittig aufgebaut, nicht aufsteigend und bildet keine Zusatz- / Geheim- Informationen über das Produkt ab.

[QuickAndDirty]

Es wird benutzt um Positions-Dubletten innehalb einer Gruppe zu verhindern. Jeder Datensatz hat eine eindeutige ID.....der Primärschlüssel halt .
Ich wusste nicht das diese Funktionalität von Schlüsseln ein Missbrauch ist. Dachte an sich immer, dass es weitverbreitete Praxis ist.

[Redeemer]

Wie können beim Löschen Dubletten auftreten?

[Jasocul]

Ich glaube fast, dass es hier ein generelles Missverständnis gibt.
Der PK ist das Feld "ID".
Dann gibt es zusätzlich eine GruppenID und eine Position, die in Kombination ein UK sind.
Wenn ich dabei an eine WaWi denke, könnte GruppenID eine Auftragsnummer sein und Position die laufende Positionsnummer.
Löscht man eine der Position (z.B. Nr 3 von 500), möchte man die Positionsnummern neu vergeben, damit keine Lücken entstehen.
Die Aussage, dass das eine Prüfung der Dubletten sein soll, ist vermutlich unglücklich formuliert. Ich gehe davon aus, dass durch den kombinierten UK doppelte Positionsnummern in einem "Auftrag" bei der Erfassung verhindert werden sollen. Es stellt sich dabei natürlichdie Frage, wie das passieren sollte, da man üblicherweise Positionsnummern nicht manuell vergibt, aber das ist ein anderes Thema.

Firebird kennt "with" und das gilt inzwischen eigentlich für alle aktuellen Datenbanken.
Nimmt man in das With eine sortierte SQL-Abfrage und joined das im Update-Statement, kann man vermutlich die Positionen neu durchnummerieren.
Bitte selbst testen, da es nicht schwierig ist und ich auch kein Firebird zur Verfügung habe, um das zu prüfen.

[QuickAndDirty]

Zitat von Jasocul:

Ich glaube fast, dass es hier ein generelles Missverständnis gibt.
Der PK ist das Feld "ID".
Dann gibt es zusätzlich eine GruppenID und eine Position, die in Kombination ein UK sind.
Wenn ich dabei an eine WaWi denke, könnte GruppenID eine Auftragsnummer sein und Position die laufende Positionsnummer.
Löscht man eine der Position (z.B. Nr 3 von 500), möchte man die Positionsnummern neu vergeben, damit keine Lücken entstehen.
Die Aussage, dass das eine Prüfung der Dubletten sein soll, ist vermutlich unglücklich formuliert. Ich gehe davon aus, dass durch den kombinierten UK doppelte Positionsnummern in einem "Auftrag" bei der Erfassung verhindert werden sollen. Es stellt sich dabei natürlichdie Frage, wie das passieren sollte, da man üblicherweise Positionsnummern nicht manuell vergibt, aber das ist ein anderes Thema.

Firebird kennt "with" und das gilt inzwischen eigentlich für alle aktuellen Datenbanken.
Nimmt man in das With eine sortierte SQL-Abfrage und joined das im Update-Statement, kann man vermutlich die Positionen neu durchnummerieren.
Bitte selbst testen, da es nicht schwierig ist und ich auch kein Firebird zur Verfügung habe, um das zu prüfen.

Ich lese mir mal "With" durch!

[QuickAndDirty]

Zitat von Redeemer:

Wie können beim Löschen Dubletten auftreten?

Beim Löschen keine!
Beim Aufrücken in die freie Position kann das passieren je nach dem in welcher reihenfolge der SQLServer die Datensätze bearbeitet.

Code:

UPDATE "TABELLE" SET "ZEILENPOSITION" = "ZEILENPOSITION" -1

Wenn UPDATE zuerst Datensätze mit der kleinsten "ZEILENPOSITION" bearbeitet dann haben wir keine Doubletten
Wenn UPDATE aber mit einer Anderen "ZEILENPOSITION" beginnt als mit der Kleinsten, dann havben wir temporär Doubletten und die UNIQUE KEY CONSTRAINT schlägt zu.