UNIQUE constraint?

[Dr.Colossos]

Hallo,

ich hätte eine Frage zu einem tabellenweiten UNIQUE constraint.

Ich habe eine Tabelle in der eine Baumstruktur (Gruppen mit Untergruppen) gehalten wird.

Um einfach Abfragen nach Gruppe mit ihren Untergruppen stellen zu können hat die Tabelle eine Spalte "path", die UNIQUE sein soll.

Die Spalte sieht in etwa so aus.

root        1
child1      1.1
child2      1.2
grandChild1 1.1.1
grandChild2 1.1.2
grandChild3 1.2.1
...

So, nun möchte ich z.B. child1 und child2 tauschen.

[pseudoSQL]
UPDATE child1 set path = '1.2' <- hier krachts schon

UPDATE child2 set path = '1.1'
[/pseudoSQL]

Ist klar, temporär ist path nicht UNIQUE. Ich dachte nun, wenn ich das in eine Transaktion packe, dann wäre das machbar, da constraints erst zu Ende der Transaktion geprüft werden.

Das ist wohl ein Irrglaube (ich nutze hier SQLite, kann aktuell kein anderes DBMS testen). Ist das eine SQLite Limitation?

Wie könnte man das umsetzen? Nur über einen temporären Wert, a là ...

[pseudoSQL]
UPDATE child1 set path = '1.X' <- unique

UPDATE child2 set path = '1.1' <- unique

UPDATE child1 set path = '1.2' <- unique, Tausch erfolgreich
[/pseudoSQL]

... bzw. mit DELETE und REINSERT (dann krachts aber evtl. mit referentieller Integrität).

Ideen?

Danke im Voraus!

Bye

P.S.:
Hab beim Posten diese Meldung bekommen:
"Der Text enthält drei oder mehr gleiche Zeichen hintereinander oder enthält keine Satzzeichen (-2.00 Punkte). Sind Sie sicher, dass Sie das Posting so abschicken wollen?

Der Betreff enthält nur große Buchstaben (-3.00 Punkte). Sind Sie sicher, dass Sie das Posting so abschicken wollen?"

Mindestens die 2. "Anschuldigung" ist ungerechtfertigt.

[dedlfix]

Hi!

So, nun möchte ich z.B. child1 und child2 tauschen.
Wie könnte man das umsetzen? Nur über einen temporären Wert, a là ...

Du kannst auch den nicht-uniquen Rest tauschen. Es sei denn, da sind weitere eindeutige Spalten dabei (beispielsweise eine ID) die wegen Verknüpfungen zu anderen Daten zugeordnet bleiben soll. Dann hilft nur der temporäre Wert oder das zwischenzeitliche Deaktivieren des Unique-Index, was aber mit Vorsicht zu genießen ist, also mindestens gegen nebenläufige Prozesse gesichert werden muss.

Hab beim Posten diese Meldung bekommen:

Einfach gar nicht ignorieren, wenn du es für nicht weiter wichtig hältst.

Lo!

[Dr.Colossos]

Hallo,

Du kannst auch den nicht-uniquen Rest tauschen. Es sei denn, da sind weitere eindeutige Spalten dabei (beispielsweise eine ID) die wegen Verknüpfungen zu anderen Daten zugeordnet bleiben soll.

Ja, hab einen generischen PK (ID) und auch der Gruppenname soll eindeutig sein. Außerdem, wenn ich mir den Code in 6 Monaten nochmal anschau, dann check ich das nicht mehr, wenn ich das so kontra-intuitiv umsetze.

... oder das zwischenzeitliche Deaktivieren des Unique-Index, was aber mit Vorsicht zu genießen ist

Hmmmm, auch sehr unhübsch.

Versteh mich bitte nicht falsch, ich bin dir sehr dankbar für die Anregungen, aber diese Lösungen sind mir nicht "sauber" genug.

Sonst fällt mir selbst höchstens noch ein AFTER INSERT/UPDATE Trigger ein, ist ober doch ein gewisser over-head.

Hat jemand eine andere, saubere und less-over-head Lösung parat?

Danke sehr!

[dedlfix]

Hi!

... oder das zwischenzeitliche Deaktivieren des Unique-Index, was aber mit Vorsicht zu genießen ist
Hmmmm, auch sehr unhübsch.

Stell dir mal vor, du wolltest zwei IDs tauschen (die ausnahmsweise mal nicht unique sind):

SET id = y WHERE id = x
  SET id = x WHERE id = y

Das zweite geht nicht mehr eindeutig, weil du nun zweimal y hast. Und am Ende ergäbe das zweimal x und kein y.

Für deinen Fall hast du jedoch noch eine eindeutig bleibende ID als Anfasser. Dein Unique-Index auf der path-Spalte weiß aber nicht, dass du für den Augenblick zwei gleiche Werte haben willst, weil du über den PK die Datensätze auseinanderhalten kannst und den doppelten Wert gleich wieder beseitigen willst. Neben der vorübergehenden Entfernung des Unique-Index und einem temporären Wert bleibt als mögliche Lösung noch die vorübergehende Entfernung der beiden Datensätze, und sie dann mit geänderten Daten in eindeutiger Weise wieder hinzuzufügen.

Lo!

[Dr.Colossos]

Hi,

[Eigenzitat]
Wie könnte man das umsetzen? Nur über einen temporären Wert, a là ...

[pseudoSQL]
UPDATE child1 set path = '1.X' <- unique

UPDATE child2 set path = '1.1' <- unique

UPDATE child1 set path = '1.2' <- unique, Tausch erfolgreich
[/pseudoSQL]

... bzw. mit DELETE und REINSERT (dann krachts aber evtl. mit referentieller Integrität).
[/Eigenzitat]

... bleibt als mögliche Lösung noch die vorübergehende Entfernung der beiden Datensätze, und sie dann mit geänderten Daten in eindeutiger Weise wieder hinzuzufügen.

Wie oben erwähnt würde das ggf. nicht mit etwaigen foreign keys funktionieren. Denkbar wären z.B. Personen die den zu tauschenden Gruppen zugeordnet sind, die müsste man dann auch löschen ...

Jemand noch mehr Ideen?

Danke!

[Ilja]

moin,

Jemand noch mehr Ideen?

änder die beiden in einem update, vielleicht schlägt dabei nicht der unique constraint an.

UPDATE tabelle
SET path = CASE WHEN path = '1.1' THEN '1.2'
                WHEN path = '1.2' THEN '1.1'
                ELSE NULL
           END
WHERE path IN ('1.2', '1.1')
;

Ilja

[dedlfix]

Hi!

änder die beiden in einem update, vielleicht schlägt dabei nicht der unique constraint an.

Das kann ich mir nur dann vorstellen, wenn es sich um eine transaktionsfähige Tabelle handelt. Denn es müsste erst pro forma eine Änderung stattfinden, dann der unique constraint geprüft werden und erst anschließend ein Schreiben stattfinden. Da aber schon wie im Ausgangsposting erwähnt eine explizite Transaktion keinen Erfolg brachte, bin ich auch skeptisch, dass das System mit dem "CASE-UPDATE" eventuell eine implizite Transaktion erstellt und obendrein dabei noch den unique constraint nicht prüft.

Lo!

[Ilja]

moin,

bin ich auch skeptisch, dass das System mit dem "CASE-UPDATE" eventuell eine implizite Transaktion erstellt und obendrein dabei noch den unique constraint nicht prüft.

nun, bei mir (oracle) geht das in einem statement, bei zwei updates geht es nicht. ob es bei ihm auch geht, keine ahnung, musss man einfach probieren.

Ilja

[Dr.Colossos]

Hi,

UPDATE "group"
SET path = CASE WHEN path = '1.4' THEN '1.1'
                WHEN path = '1.1' THEN '1.4'
                ELSE NULL
           END
WHERE path IN ('1.1', '1.4');

--> Fehler bei Ausführung der Abfrage: column path is not unique

SQLite 3 unterstützt das nicht, auch nicht wenn ich es in eine Transaktion einschließe.

Geht also nur mit umweg ... oder hat noch wer eine andere Idee?

Danke sehr!

[dedlfix]

Hi!

SQLite 3 unterstützt das nicht, auch nicht wenn ich es in eine Transaktion einschließe.
Geht also nur mit umweg ... oder hat noch wer eine andere Idee?

<del>Du könntest an SQLite vorbei direkt in der Datendatei die paar Bytes umschreiben.</del>

Auf was für eine Idee hoffst du? Dass irgendwie doch die Unique-Constraint-Prüfung umgangen werden kann? Oder dass sie in bestimmten Situationen nicht zuschlägt? Letzteres würde ich als Fehler werten, offenbart das doch eine Situation, die nicht beachtet wurde. Dass selbst innerhalb einer Transaktion SQLite keine Unique-Constraint-Verletzung gestattet, Oracle aber anscheinend doch, ist aus deiner Sicht ärgerlich, aber nicht zu ändern.

Wenn es noch weitere Lösungen geben sollte, dann sind sie vermutlich noch weiter hergeholt und sicher weniger gut verständlich, als wenn du dir aus den bereits bekannten die einfachste (im Sinne von zu implementieren _und_ zu verstehen - Kompromisse zwischen beiden Anforderungen eingeschlossen) nimmst.

Lo!

[Dr.Colossos]

Hi,

ich frage nach, weil ich nicht so arogant bin, dass ich glauben würde ich wisse schon alles auf diesem Gebiet, und gleichzeitig aus dem Vertrauen heraus, dass es da draußen noch jemand gibt der bzgl. SQLite mehr weiß.

Auch kann es ja sein, dass ich konzeptionell was falsch mache, oder zumindest nicht zu 100% richtig, wodurch das problem von vornherein ausgeschlossen wird.

Ich wüsst jetzt nicht was an meinem Herangehen verwerflich sei, aber wenn du meinst hier wäre alles gesagt, dann schließe ich mich deiner Meinung an.

Danke an alle!

[CLOSED]

[dedlfix]

Hi!

Auch kann es ja sein, dass ich konzeptionell was falsch mache, oder zumindest nicht zu 100% richtig, wodurch das problem von vornherein ausgeschlossen wird.

Du könntest auf Nested Sets umsteigen, dann brauchst du keine Pfadangabe extra zu pflegen, hast vielfältige Abfragemöglichkeiten, musst aber beim Ändern aufpassen (atomare Vorgänge erstellen (Sperren setzen und/oder Transaktionen verwenden)). Dokumentationen und beispielhafte Abfragen dazu lassen sich einige finden.

Ich wüsst jetzt nicht was an meinem Herangehen verwerflich sei, aber wenn du meinst hier wäre alles gesagt, dann schließe ich mich deiner Meinung an.

Ich denke, das hat auch was mit beobachten und schlussfolgern zu tun. Und ich fragt auch nach deinen Gedankengängen, was du dir so vorstellen kannst, wie man noch zwei Werte tauschen kann.

Es hat sich ja schon gezeigt, dass SQLite es nicht einmal vorübergehend mag, wenn man einen Unique Constraint verletzt. Also kann es keine Lösungen geben, die eine solche Verletzung vornehmen. Wenn doch, muss es ein Fehler sein, denn das bedeutete inkonsistentes Verhalten, was den Unique Constraint angeht. Ich würde mich nicht auf solches Verhalten verlassen wollen oder darauf spekulieren, dass es in späteren Versionen Bestand hat. Also bleiben nur Lösungen, die den Unique Constraint vorübergehend deaktivieren oder ihn nicht verletzen. Und da gibt es nichts weiter außer den genannte Methoden. Das Tauschen von zwei Werten geht nur über einen dritten oder wenn man beide gleichzeitig ändert, was aber eben hier nicht mit einem UPDATE geht, weil bei den beiden impliziten Teilvorgängen eben die Verletzung stattfindet. Bleibt noch beide zu entfernen und geändert nacheinander wieder einzusetzen (oder mit einem Multi-Insert).

Lo!

[Dr.Colossos]

Hallo,

UPDATE tabelle
SET path = CASE WHEN path = '1.1' THEN '1.2'
                WHEN path = '1.2' THEN '1.1'
                ELSE NULL
           END
WHERE path IN ('1.2', '1.1')

Yikes !?!? Ist das SQL-Standard? Kannte ich bisher echt nicht (UPDATE SET CASE WHEN).

Danke sehr, probier ich gleich wenn ich daheim bin.

[EKKi]

Mahlzeit Dr.Colossos,

Yikes !?!? Ist das SQL-Standard? Kannte ich bisher echt nicht (UPDATE SET CASE WHEN).

Es gibt kein "UPDATE SET CASE WHEN". Es gibt lediglich ein "UPDATE SET [...]". Als Wert für eine Spalte kannst Du aber nahezu beliebige Sprachkonstruktionen verwenden - Hauptsache, sie liefern den korrekten Datentyp zurück.

MfG,
EKKi

[Vinzenz Mai]

Hallo,

Yikes !?!? Ist das SQL-Standard?

ja, CASE WHEN ist SQL-Standard und nichts Besonderes.

Freundliche Grüße

Vinzenz