Firebird Feld mit foreign key "0" anstelle von NULL

[Emwykey]

Hallo zusammen,

ich habe eine einige Felder in meiner Datenbank welche ich überarbeiten möchte. Ziel ist es, dass über eine Fremdschlüssel Definition verhindert wird, dass der Master Datensatz gelöscht werden kann, sofern er in einer anderen Tabelle referenziert ist.

Das Problem ist, dass ich sofern kein Referenzdatensatz gewählt wird überall 0 anstelle von NULL gespeichert habe - warum sei jetzt mal dahin gestellt.

Jetzt will mir die Anlage der Fremdschlüssel nicht gelingen, da es keinen Datensatz mit der ID 0 in den Referenztabellen gibt.
Gibt es eine Möglichkeit die Datenbank so einzustellen, dass 0 wie auch NULL in diesem Bezug als Leer - Wert akzeptiert werden?

Danke im Vorraus und einen schönen Abend!

[himitsu]

FK zeigt auf etwas und wenn das nicht existiert, dann knallt es. (Ausnahme: NULL = nichts)
Also per se ist deine Definition falsch und alle 0 müssen NULL sein.

Ich weiß nicht ob/wie es mit ForeignKey geht, aber bei UniqueKey kann man in der Definition anstatt dem

Delphi-Quellcode:

feldname

auch z.B.

Delphi-Quellcode:

nullif(feldname, 0)

verwenden, womit der Index dort dann als NULL gespeichert wird.
Auch andersrum geht es beim UK, also z.B.

Delphi-Quellcode:

coalesce(feldname, 0)

und schon können auch NULL-Werte nicht mehrfach vorhanden sein. (NULL wird ja normalerweise von der Unique-Prüfung ignoriert)

[Emwykey]

Hallo,

danke für die Antwort.
In welcher Firebird Version geht das? Ich hab aktuell die 2.5 installiert und da lässt er das nicht zu.
:(

[hoika]

Hallo,
das alles klappt aber nur (NullIf), wenn die Tabelle erzeugt wird.

Warum bereinigst Du deine Tabelle nicht vor dem Erzeugen des FK
Update Table Set FKFeld=Null Where FKFeld=0

[himitsu]

Zitat:

Zitat von hoika (Beitrag 1409134)

das alles klappt aber nur, wenn die Tabelle erzeugt wird.

Wenn der Index erzeugt (oder geändert) wird oder wenn nachfolgen Datensätze angelegt/geändert werden.

Wie gesagt, was und wie Firebird genau kann, weiß ich auch nicht, aber hier mal ein Beispiel aus PostgreSQL.

SQL-Code:

CREATE UNIQUE INDEX co_data_index ON component_options (co_item, COALESCE(co_minr, -2), COALESCE(LOWER(co_name), ''));

Aber viele andere DBMS können ebenfalls mit Funktionen in den Definitionen von Indize und Constraints umgehen und da denke ich mir, dass Firebird hier keine Ausnahme sein wird.


Aber deswegen auch zuerst der Vorschlag die Daten zu bereinigen, weil das bestimmt einfacher/besser sein wird.

[Delphi.Narium]

Einzige Krücke, die mir da so einfällt:

Mastersatz mit der ID 0 anlegen und kenntlichmachen, dokumentieren, dass dies die Referenz für die Sätze ist, für die es keine Referenz gibt und diesen vor dem Löschen (ggfls. auch Ändern) schützen, da ansonsten ggfls. mit ihm zusammen alle (eigentlich) nicht referenzierten Daten aus der Datenbank entfernt werden (was ja per Constrains durchaus umsetzbar ist).

Wenn Du dem Vorschlag die ID 0 auf Null zu setzen nicht folgen kannst, weil z. B. andere Nutzer / andere Software bei fehlender Referenz den Wert 0 für die ID vergeben und Du keine andere Lösung findest, das Problem per Constrains ... zu lösen, scheint das (erstmal) die einzig praktikable Lösung.

Prinzipiell sollte man sich aber nochmal grundlegende Gedanken über das vorhandenen Design der Datenbank und / oder der sie benutzenden Software machen. Ansonsten besteht die Gefahr, dass man bei mehr oder weniger dokumentierten oder undokumentierten Krücken zur Problembehebung bleibt.

Und sowas geht irgendwann schief, Murphy ist da geduldig und schlägt irgendwann gnadenlos zu ;)

[mkinzler]

Ggf. anderes Programm per Trigger / View befriedigen.

Insert 0 -> NULL, Abfrage NULL -> 0.

[Neutral General]

Hab ich was verpasst oder würde ein einfaches

Code:

UPDATE
  tabelle
SET
  fk_feld = null
WHERE fk_feld = 0

nicht das Problem lösen?

[Delphi.Narium]

Wenn irgend ein Programm beim Einfügen neuer, nicht referenzierter, Sätze wieder 'ne 0 für die ID vergibt, wäre das nur 'ne Momentlösung. Und wenn es dann mit ID = Null nicht zurecht kommt, eher kontraproduktiv.

Das

Zitat:

Zitat von Emwykey

- warum sei jetzt mal dahin gestellt.

aus der Threaderöffnung ist genau der Grund, weshalb man keine definitiv funktionierende Lösung vorschlagen kann.

Also benötigen wir erstmal eine Erklärung des Warum, um dann daraus eventuell eine Lösung, besteht aus einer Kombination der bisherigen Vorschläge (Update, Trigger, View, Constraint ...), entwerfen zu können.

[Neutral General]

Naja gut, dass man jegliche Trigger/Prozeduren und Programmcode dementsprechend ändern musst ist klar.
Ich meine um ehrlich zu sein gibt es keine andere ordentliche Lösung als das UPDATE + Codeänderungen (0 => null)
Alles andere wäre nur ein Workaround den man früher oder später eh wieder bereut.
Anders ist es ggf. weniger Arbeit aber man verarscht sich nur selbst.

[Delphi.Narium]

Du sagst prizipiell in etwas krasserer Form das, was ich weiter oben meinte :-)

Zitat:

verarscht

ist quasi der Klartext für:

Zitat:

Und sowas geht irgendwann schief, Murphy ist da geduldig und schlägt irgendwann gnadenlos zu ;-)

[p80286]

Also wenn ich #1 richtig verstanden habe, gibt es Detail-Datensätze ohne Verbindung zum Haupt-Datensatz, was ja eher suboptimal ist. Da sollte wohl mal massiv aufgeräumt werden.

Gruß
K-H

P.s
Fehler machen wir alle, aber diese zukleistern ist nur wenig schlechter als Garnichts zu tun.

[jobo]

Wenn Du nicht willst, dass der Datensatz gelöscht wird, lege ein Feld an, dass diesen Status (und vielleicht andere Lifecycle Zustände des Datensatz definiert) und nutze einen Trigger, der das überwacht.

Die Referenz auf eine andere Tabelle dafür zu verwenden, ist besonders in Deinem Fall nicht möglich, außer Du legst eben den Datensatz mit Key 0 in der Fremdtabelle an.

Funktionsbasierte Indices würde ich für den Zweck auch nicht einsetzen- selbst wenn es ginge, was ich bezweifele- weil ein Index nichts mit dem Modell zu tun haben sollte.

[IBExpert]

Ein Foreign Key ist technisch nichts anderes als ein Systemtrigger und ein Systemindex.
Es spricht nichts dagegen, den Trigger so umzusetzen, das der deiner Logik entspricht
und zum Beispiel mit einer Exception das Löschen verhindert

Code:

CREATE OR ALTER TRIGGER CUSTOMER_BD0 FOR CUSTOMER
ACTIVE BEFORE DELETE POSITION 0
AS
begin
  if (exists (select orders.customer_id from orders where coalesce(orders.customer_id,0)=old.id)) then
  exception err 'Customer kann nicht gelöscht werden, wenn noch noch Orders existieren';
end

das ist kein konkreter Vorschlag für deine Problematik, das so mit coalesce zu lösen, sondern
einfach nur ein Hinweis, das fehlende Logik in Foreign keys problemlos selbst gebaut werden kann.

Wenn hier im Beispiel kein Index auf orders.customer_id existieren würde, bricht die Performance
ein und jeder delete auf customer würde ein Full table scan auf orders auslösen. Daher sollte man
drauf achten, das da ein Index existiert. Die Deklaration eines Foreign Keys legt Trigger und
Index automatisch an, wenn es also dir so nicht passt, wie es deklarativ geht, do it yourself.

[Emwykey]

Danke erstmal für eure Antworten.

Generell ist es schon machbar die 0 Werte in NULL Werte zu ändern, der Aufwand wäre nur zeitlich sehr groß da es doch ziemlich viele Felder betrifft, weswegen ich wissen wollte ob ich noch andere (weniger aufwändige) Möglichkeiten habe. Diese Frage scheint damit beantwortet zu sein :)

[himitsu]

Dennoch könnte man sich jetzt überlegen das zu ändern,
denn wer weiß wieviele hundert Felder die nächsten Jahre noch dazu kommen,
und dann wird es immer schwerer zu ändern.

[jobo]

Zitat:

Zitat von Emwykey (Beitrag 1409207)

..zu ändern, der Aufwand wäre nur zeitlich sehr groß da es doch ziemlich viele Felder betrifft

Mmh, "großer Aufwand " klingt jetzt irgendwie nach einer Crowd Sourcing Aktion bei der 20 unterbezahlte "Eingabekräfte" Formulare durchackern und statt Nichs "0" eintippen.

Man kann mit SQL sowohl horizontal als auch vertikal ganz viele Felder "auf einen Schlag" aktualisieren.
Bei einem verteilten System müsste das durch eine Aktualisierung, einen Remote Support o.ä. erfolgen.

Ansonsten sehe ich es wie himitsu und würde nicht unbedingt die technischen Möglichkeiten in der Richtung ausreizen und eine bereits unkonventionelle Umsetzung noch weiter verändern.

[Emwykey]

Zitat:

Zitat von jobo (Beitrag 1409220)

Man kann mit SQL sowohl horizontal als auch vertikal ganz viele Felder "auf einen Schlag" aktualisieren.
Bei einem verteilten System müsste das durch eine Aktualisierung, einen Remote Support o.ä. erfolgen.

Ansonsten sehe ich es wie himitsu und würde nicht unbedingt die technischen Möglichkeiten in der Richtung ausreizen und eine bereits unkonventionelle Umsetzung noch weiter verändern.

Den Inhalt der Felder und deren Definition zu ändern ist weniger das Thema, eher die ganzen Routinen die darauf zugreifen und 0 eben als LEER interpretieren, NULL aber garnicht prüfen, da die Felder alle als not NULL definiert sind ...

Aber scheint als ob ich da wohl nicht drum rum komme...

[mkinzler]

Einfach SP oder VIEW, der das gewünschte Format liefert.

[himitsu]

Zitat:

Zitat von mkinzler (Beitrag 1409230)

Einfach SP oder VIEW, der das gewünschte Format liefert.

Da das Programm auch schreiben will, wäre das dann ein "Updatable VIEW".

[p80286]

Die Idee mit der View gefällt mir, (es werden wohl mehrere werden)
aber

Zitat:

Zitat von Emwykey (Beitrag 1409226)

eher die ganzen Routinen die darauf zugreifen und 0 eben als LEER interpretieren, NULL aber garnicht prüfen, da die Felder alle als not NULL definiert sind ...

Was heißt das konkret?
Prüft Dein Delphi-Programm auf NULL/0 ? Wenn ein (FK)Feld mit NULL belegt ist, wird der Datensatz nicht ausgegeben (es sei denn mit Hilfe von z.B. "Left Join" hilft man nach)

Gruß
K-H

[Emwykey]

Zitat:

Zitat von p80286 (Beitrag 1409268)

Die Idee mit der View gefällt mir, (es werden wohl mehrere werden)
aber

Zitat:

Zitat von Emwykey (Beitrag 1409226)

eher die ganzen Routinen die darauf zugreifen und 0 eben als LEER interpretieren, NULL aber garnicht prüfen, da die Felder alle als not NULL definiert sind ...

Was heißt das konkret?
Prüft Dein Delphi-Programm auf NULL/0 ? Wenn ein (FK)Feld mit NULL belegt ist, wird der Datensatz nicht ausgegeben (es sei denn mit Hilfe von z.B. "Left Join" hilft man nach)

Gruß
K-H

--> ich will alle datensätze mit oder ohne Referenz Datensatz

Code:

Select * from Tabelle where Feld_ID = 0; Select * from Tabelle where Feld_ID <> 0;

alle SQLs die so aussehen oder eben ähnlich müssen geändert werden.

Alle Trigger die auf <> 0 oder = 0 prüfen müssen angepasst werden.

Alle speichern Routinen die 0 schreiben bei einem leeren auswahlfeld müssen geändert werden usw. -- das mein ich damit :)

Das muss aber auch bei dem View.. daher besser gleich richtig