FB: Unique Index auf Datum und Uhrzeit

[Delphi.Narium]

Ja, aber im Mehrbenutzerbetrieb dürfte das Unterfangen, Dubletten hauptsächlich per Programmcode zu ermitteln, eher schwierig werden, auf jeden Fall keinesfalls verlässlich.

In 'ner DB-Exception steht gewöhnlich drin, was passiert ist, Schlüsselverletzung ...

Wenn ich also einen Datensatz speichere und dann als Exception einen Constraintverletzung bekomme oder eine Verletzung eines eindeutigen Schlüssels, weiß ich schon, dass es sich um eben diese handelt. Und im Zusammenhang mit der Fachlichkeit, bei der der Fehler auftritt, sollte ich damit durchaus in der Lage sein, dem Anwender eine aussagefähige Meldung zu geben bzw. im Programm entsprechend zu reagieren.

Wenn man im Programm vor dem Speichern auf eventuell mögliche Schlüsselverletzungen prüft, so kann man dann zwar auch schon reagieren, muss aber beim Speichern trotzdem nochmal mit dem Auftreten einer entsprechenden DB-Exception rechnen und die dann auch vernünftig und aussagefähig für den Anwender handhaben.

An einer vernünftigen Verarbeitung von Datenbankfehlern kommt man also nicht vorbei. Die vorherige Prüfung ausserhalb der Datenbank ist also höchstens ein "Nice To Have", dass den Programmierer beruhigt, aber keinesfalls ein sicherer Umgang zur Vermeidung von Dubletten.

Die ausschließliche oder überwiegende Prüfung von Schlüsselverletzungen im Programm kann man (meiner Meinung nach) nur dann ernstlich in betrachtziehen, wenn absolut sichergestellt ist, dass eine Datenbank nur von einem Programm und einem Nutzer zeitgleich genutzt wird, also ein exklusiver Zugriff auf die Datenbank vorliegt.

[jobo]

Zitat von hoika:

Hallo,

Zitat:

Vor dem Insert manuell auf Eindeutigkeit zu prüfen macht m.E. keinen Sinn

Doch macht es, weil man dann eine schöne Meldung bringen kann ala "Datensatz bereits vorhanden".
Aus der DB-Exception herauszufinden, warum der Datensatz nicht angelegt werden konnte, ist schwerer.

Also es gibt nur wenige Meldungen, die so eindeutig sind wie eine Primärschlüsselverletzung- Fehlermeldungen bestehen ja originär aus einer Nummer, die man in der Exception abfangen kann. Die Natur des Fehlers "Primärschlüsselverletzung" bedeutet prinzipbedingt, dass auch keine weiteren Fehler aus der Anweisung folgen.
Es besteht also gar nicht die Möglichkeit, dass das System andere Fehler liefert, falsche Fremdschlüssel oder was auch immer bemeckert, weil es soweit gar nicht kommt.

Und es gilt auch was Delphi.Narium schreibt, in einem Mehrbenutzersystem ist eine manuelle Duplikat-Prüfung Glückssache. Außer:
- manuelle Prüfung und tatsächliches Insert werden in einer Transaktion durchgeführt
- die fachliche Datenlage erlaubt gar keine Duplikate (dann brauche ich aber auch keine Prüfung)

Im Zusammenhang mit der Thematik Date/Time Value als Teil des Primärschlüssels kommt hinzu, das die Fachlichkeit unscharf wird und sich die Frage stellt, was dieses Konstrukt eigentlich regulieren soll. Eine Art Floodlimit, ..?

Wenn 2 Sachbearbeiter in einem Büro einen Stapel von Bauanträgen eintippen, kann nichts passieren.
Wenn 2 Verkäufer am Telefon eines Verkaufssenders die letzten 3 Superdampfbügeleisen verkaufen, sieht es allerdings anders aus.

[himitsu]

Zitat von jobo:

In Oracle kann Date Type auch Uhrzeiten enthalten.

Das wäre auch besser, denn sein Uhrzeit-Feld ist ja auch als DATE und nicht als TIME deklariert.

Jupp, also theoretisch würde der Index ja funktionieren. (aber wie bereits genannt wurde, gibt es das Hindernis mit den ungenauen Werten)
CREATE UNIQUE INDEX DeineTabelle_id_datetime ON DeineTabelle (PersonalId, Datum, Uhrzeit)

Aber zur Sicherheit kannst du deine Zeit-Werte auch im Index umrechnen/casten, zusammenfassen und vor allem auf ein gewünschtes Maß "runden".
z.B. beim Datum die eventuelle Uhrzeit entfernen und die Uhrzeit auf Sekunden runden. (Trunc/Extract)

SQL-Code:

CREATE UNIQUE INDEX DeineTabelle_id_datetime ON DeineTabelle COMPUTED BY (PersonalId, CAST(Datum AS DATE), CAST(Uhrzeit AS TIME));  -- Cast geht wohl nicht, wenn DATE = DATETIME
CREATE UNIQUE INDEX DeineTabelle_id_datetime ON DeineTabelle COMPUTED BY (PersonalId, Trunc(Datum) + (Uhrzeit - Trunc(Uhrzeit)));
CREATE UNIQUE INDEX DeineTabelle_id_datetime ON DeineTabelle COMPUTED BY (PersonalId, DateAdd(Trunc(Datum), Uhrzeit));
...

Einen Cast/Round um Uhrzeit auf Sekunden zu runden oder ein Cast(Extract(Uhrzeit, SecondOfDay) AS INTEGER) gibt es wohl nicht?

Zitat:

Aus der DB-Exception herauszufinden, warum der Datensatz nicht angelegt werden konnte, ist schwerer.

Gut, man könnte auch über einen BeforeInsert-Trigger alles "nochmals" prüfen und eine bessere Fehlermeldung ausgeben.
Genauso kann man aber dem UniqueIndex auch einen sprechenden Namen geben, da er doch in der Fehlermeldung genannt wird.
Man könnte sich auch in die Error-Events der Connection/Querykomponente hängen und da den Triggernamen übersetzen, bzw. die Fehlermeldung um etwas Verständlicheres ergänzen.

[IBExpert]

was man wissen sollte:

Primärschlüssel und Unique Constraints werden von Firebird auch ohne commit auf Eindeutigkeit geprüft.

Kann man relativ einfach verstehen wenn man mit IBExpert oder isql 2 Instanzen öffnet und auf einer
Tabelle mit PK oder unique constraint in beiden Sitzungen einen identischen Insert macht, ohne den
zu committen.

Ablauf Beispiel auf einer leeren Tabelle mit PK auf der ID Spalte
create table tab(id bigint not null primary key);

1. Client 1: Select * from Tab where id=1;
2. Client 1: Select ausführen starten automatisch eine Transaktion für diese connection
3. Client 1: Weil die Tabelle beim Start leer war kommt kein Ergebnis

4. Client 2: Select * from Tab where id=1
5. Client 2: Select ausführen starten automatisch eine Transaktion für diese connection
6. Client 2: Weil die Tabelle beim Start und immer noch leer ist kommt kein Ergebnis

7. Client 1: insert into tab(id) values (1);
8. Client 1: Befehl wird fehlerfrei ausgeführt, aber Transaktion ist noch aktiv.
9. Client 1: Select * from Tab where id=1;
10.Client 1: In dieser Session sieht Client 1 den selbst erzeugten Datensatz auch wenn noch kein commit erfolgt ist

11. Client 2: Select * from Tab where id=1;
12. Client 2: Obwohl in der Tabelle schon ein Datensatz von Client 1 enthalten ist, sieht client 2 den nicht, weil der noch nicht committed ist (Stichwort MGA Multigenerations Architektur)
13. Client 2: Da kein Datensatz sichtbar ist versucht Client 2 den anzulegen
14. Client 2: insert into tab(id) values (1);
15. Client 2: Befehl wird sofort mit Fehler beendet, weil schon eine PK Verletzung vorliegt, auch wenn der konkurrierende Datensatz noch nicht committed ist.

Technisch wird der PK Wert schon in den Unique Indexbaum eingetragen, auch ohne commit. Wenn da schon einer ist, knallt es bei Unique.

Genau für solche Zwecke wurden die Sequenzen/Generatoren entworfen. Über den serverseitig serialisierten Aufruf der Funktion GEN_ID() wird Erhöhen, zurückschreiben und Ergebnis anzeigen unabhängig von Transaktionen netzwerksicher implementiert.

Zurück zur Ausgangsfrage Timestamp als Unique: CURRENT_TIMESTAMP unterstützt maximal Millisekunden. Also sind maximal 1000 unterschiedliche Werte pro Sekunde möglich. Die Auflösung der Zeitfunktionen unter Windows ist aber wesentlich geringer, man liest im Schnitt von 15ms und das entspricht auch meiner Erfahrung. Wer Spaß dran hat kann ggf eigene Datentypen nutzen und eine nanosekunden genaue Zeit über assembler funktionen abfragen (geht ggf auch als udf), löst aber das Problem nicht, weil auch da kein Unique garantiert werde kann.

Einen Zeitstempel als Unique anzunehmen ist bei langsam eintrudelnden Daten von nur einer Quelle durchaus denkbar, aber im Netzwerkbetrieb und bei schneller Datenerzeugung der falsche Weg (nicht nur unter Firebird)

[p80286]

In einigen vorigen Beiträgen wurde auf den Primärschlüssel hingewiesen. Der ist in diesem Zusammenhang irrelevant.

Zitat:

gegeben ist folgende Tabelle:

Id Integer
PersonalId Integer
Datum Date
Uhrzeit Date

So wie es oben schon steht, suche ich eine Möglichkeit, zu verhindern,
dass zur gleichen PersonalId ein Eintrag mit gleicher PersonalId, Datum und Uhrzeit erzeugt wird

Es könnte also sein, daß Datensätze generiert werden (von wem auch immer), in denen PersonalID,Datum,Uhrzeit gleich sind. Diese Doubletten dürfen nicht gespeichert werden.
Also sollte die Auflösung der Uhrzeit so groß sein, daß doppelte Werte nicht möglich sind. Ist das nicht möglich, sollte eine Hilfskonstruktion wie z.B. ein Index-Feld (integer) für gleiche Datensätze verwendet werden. Oder aber es wäre zu Überlegen ob Uhrzeit überhaupt das richtige Unterscheidungskriterium wäre.

Gruß
K-H

[jobo]

Zitat von p80286:

In einigen vorigen Beiträgen wurde auf den Primärschlüssel hingewiesen. Der ist in diesem Zusammenhang irrelevant.

Zitat:

gegeben ist folgende Tabelle:

Id Integer
PersonalId Integer
Datum Date
Uhrzeit Date

So wie es oben schon steht, suche ich eine Möglichkeit, zu verhindern,
dass zur gleichen PersonalId ein Eintrag mit gleicher PersonalId, Datum und Uhrzeit erzeugt wird

Es könnte also sein, daß Datensätze generiert werden (von wem auch immer), in denen PersonalID,Datum,Uhrzeit gleich sind. Diese Doubletten dürfen nicht gespeichert werden.
Also sollte die Auflösung der Uhrzeit so groß sein, daß doppelte Werte nicht möglich sind. Ist das nicht möglich, sollte eine Hilfskonstruktion wie z.B. ein Index-Feld (integer) für gleiche Datensätze verwendet werden. Oder aber es wäre zu Überlegen ob Uhrzeit überhaupt das richtige Unterscheidungskriterium wäre.

Der Primärschlüssel wird ja diskutiert, ob er geeignet ist das Ziel zu erreichen. Aber Deine Ausführungen streifen m.E. wie einige andere Beiträge einen Knackpunkt, der vielleicht bei der Auflösung hilft.
Der exakte, gewünschte Nutzen von Datum/Uhrzeit als sagen wir mal "Hilfsmittel" zur Eindeutigkeit ist eigentlich gar nicht klar:

Soll maximale Auflösung erreicht werden?
Oder soll im Gegenteil eine Beschränkung stattfinden?

Im ersten Fall komme ich selbst durch Kombination mit den anderen Schlüsselfeldern irgendwo an eine Grenze. Datum/Uhrzeit/Sekunden/Millisekungen* sind bei dem Datentyp beschränkt.
Im 2. Fall kann ich durch Beschneiden der Uhrzeit definieren, wie viel Datensätze es denn sein dürfen. Eine Kürzung des gespeicherten Wertes z.B. auf Sekunden würde ergeben, dass nicht mehr als 60 Werte pro Minute (in Kombination mit den anderen Schlüsselfeldern) gespeichert werden können.