Fehlerhaften ForeinKey finden

[Perlsau]

Moin allerseits,

in einer DB gibt es eine Tabelle ADRESSEN, die eine Spalte ORT_ID enthält, welche auf die Tabelle ORTE verweist, in der Orte, Postleitzahlen und Vorwahlen eingetragen werden. Die Spalte ORT_ID ist jedoch nicht als Foreign-Key deklariert, die entsprechende Zuweisung erfolgt erst im zugehörigen View.

Nun habe ich festgestellt, daß in View_ADRESSEN genau eine Adresse weniger angezeigt wird als in der Tabelle ADRESSEN. Wie das zustandekam, konnte ich mir leicht erklären, denn ich habe gestern den halben Tag damit verbracht, die Tabelle ORTE zu bereinigen, weil dort etliche Orte quasi doppelt vorhanden waren: gleiche Postleitzahl mit anderer Schreibweise des Ortes (z.B. München-Schwabing und München mit derselben PLZ). Ich habe also irgendwo vergessen, einer Adresse die verbliebene ORT_ID zuzuweisen (z.B. München-Schwabing gelöscht, aber die Adresse mit dieser ORT_ID nicht geändert), so daß diese eine Adresse nun eine ORT_ID aufweist, die in der Tabelle ORTE nicht mehr existiert. Wie finde ich diesen Record heraus, ohne händisch alle Tabellen durchgehen zu müssen?

Besten Dank für euer Interesse.

[Olli73]

Code:

select * from ADRESSEN where ORT_ID not in (select ORT_ID from ORTE)

(falls ich dein Problem richtig verstanden habe)

[Uwe Raabe]

Oder so:

Code:

select * 
from adressen a
left join orte b
on a.ort_id = b.<???>
where b.<???> is null

Heißt das ID-Feld in Orte auch ORT_ID?

[Uwe Raabe]

Für's nächste mal:

[Perlsau]

Zitat:

Zitat von Olli73 (Beitrag 1313868)

Code:

select * from ADRESSEN where ORT_ID not in (select ORT_ID from ORTE)

(falls ich dein Problem richtig verstanden habe)

Du hast mein Problem vollkommen richtig verstanden und die perfekte Lösung dafür präsentiert. :thumb:
Ich konnte den fehlerhaften Record finden und korrigieren.
Nun werden in der Originaltabelle genau so viele Records angezeigt wir im View. Herzlichen Dank dafür :love:

Zitat:

Zitat von Uwe Raabe (Beitrag 1313869)

Oder so:

Code:

select * 
from adressen a
left join orte b
on a.ort_id = b.<???>
where b.<???> is null

Dank auch dir :thumb:
Die Version von Olli empfinde ich jedoch als handlicher, weil sie kürzer ist (und nicht, weil sie früher da war).

Zitat:

Zitat von Uwe Raabe (Beitrag 1313869)

Heißt das ID-Feld in Orte auch ORT_ID?

Nein, ich habe Phantasie-Bezeichner gewählt, um das Problem so anschaulich wie möglich darzustellen: Tabellen und Spalten lauten bei mir anders. Dennoch war ich in der Lage, die gepostete Lösung erfolgreich auf mein Problem anzuwenden. Ich gehe sogar davon aus, daß ich diese Lösung auch in zukünftig auftretenden ähnlichen Fällen anzuwenden in der Lage sein werde. Aber dennoch danke für deine fürsorgliche Nachfrage :-D

Zitat:

Zitat von Uwe Raabe (Beitrag 1313870)

Für's nächste mal:

Danke auch dafür, das kann ich gut gebrauchen. :thumb:
Hab's mir gleich runtergeladen, ausgedruckt und an die Wand gepinnt.

[p80286]

Die "not in" Lösung stoßt aber eher an die Grenzen der DB (Begrenzung der Menge), darum wäre die Lösung von Uwe zu bevorzugen.

Gruß
K-H

[Perlsau]

Zitat:

Zitat von p80286 (Beitrag 1313912)

Die "not in" Lösung stoßt aber eher an die Grenzen der DB (Begrenzung der Menge), darum wäre die Lösung von Uwe zu bevorzugen.

Versteh ich jetzt nicht: welche Grenzen? Ich hab da ungefähr 2500 Datensätze in der Adresstabelle und ca. 1400 in der Orttabelle. Erstere wird allerhöchstens 50.000 Datensätze haben (mehr gibt's in dieser Branche nicht in DE), letztere vielleicht ... keine Ahnung, aber keine 20.000 schätze ich mal ... Schließlich hab ich das bis jetzt nur einmal benötigt, um in IbExpert im SQL-Editor den fehlerhaften Record zu finden. Das ging so schnell, ich hatte kaum Zeit zum Blinzeln :lol:
Also warum sollte ich mir da jetzt tiefere und noch weiter tiefergehende Gedanken machen müssen :?:

[jobo]

Zitat:

Zitat von Perlsau (Beitrag 1313914)

Zitat:

Zitat von p80286 (Beitrag 1313912)

Die "not in" Lösung stoßt aber eher an die Grenzen der DB (Begrenzung der Menge), darum wäre die Lösung von Uwe zu bevorzugen.

Versteh ich jetzt nicht: welche Grenzen?

Versteh es einfach als freundlichen Hinweis bzw. Warnung. Wie Deine konkrete Datensituation in Zahlen aussieht, kann ja niemand vorher wissen. Die Grenzen sind wie immer DB und Versionsabhängig.
Auf die Schnelle hab ich zu Firebird nichts konkretes außer diesem Post gefunden:
https://groups.yahoo.com/neo/groups/...s/topics/95414

Dabei ist nicht klar, ob die Limitierung nur für explizite Aufzählung oder auch für dynamische Selectrückgaben erfolgt.

Meine Verständnis ist:
(not) in .. für kleineren Abgleich (also adhoc, kleine Komfortabfragen)
table join .. für unlimitierten Massenabgleich (also für den Rest)

Wenn Du es genau für Deine Version wissen willst, musst Du wohl selbst danach suchen oder es ausprobieren.

[mkinzler]

Da rächt sich die Ansicht, dass man man aus Zeit-/Kostengründen beim Datenbankdesign auf die Deklaration der Constraints VWzüchten kann, da man diese ja im Programm berücksichtigt.

[jobo]

Zitat:

Zitat von mkinzler (Beitrag 1313921)

VWzüchten

Bitte nicht mit Handy von Unterwegs auf der Autobahn posten, schon gar nicht, wenn es ein VW ist. Da geht nicht nur die Worterkennung schnell mal schief.
;)

Zitat:

Zitat von mkinzler (Beitrag 1313921)

Da rächt sich die Ansicht..

Naja, das wäre eigentlich die Hauptbotschaft des Threads, der sich originär um die Beseitigung der Folgen dreht!

[Perlsau]

Zitat:

Zitat von mkinzler (Beitrag 1313921)

Da rächt sich die Ansicht, dass man man aus Zeit-/Kostengründen beim Datenbankdesign auf die Deklaration der Constraints VWzüchten kann, da man diese ja im Programm berücksichtigt.

Oh, da bist du aber leicht im Irrtum: Selbstverständlich existiert ein eindeutiger Index Ortname-PLZ, so daß dieselbe Variante nicht doppelt vorkommen kann. Die Daten stammen allerdings aus einer Liste, die durch Selbstauskunft von Mitgliedern einer bestimmten Branche entstand – daher diese verschiedenen Schreibweisen für die Orte: Die meisten schreiben zwar ihre PLZ richtig, hängen dann aber ihren Ortsteilbezeichner noch hintendran oder verwenden nur den Ortsteil, z.B. 76227 Durlach oder 76227 Karlsruhe-Durlach oder 76227 Karlsruhe/Durlach statt einfach 76227 Karlsruhe. Ähnliches haben wir hier bei den Telefon-, Fax- und Handynummern. Hier halte ich mich an eine Vorwahlenliste, mit deren Hilfe ich die Vorwahlen abtrenne und mit der Vorwahl, die der PLZ zugeordnet ist, vergleiche. Records, die hier Diskrepanzen aufweisen, werden markiert und müssen später einzeln verifiziert werden (Branchenbuch, Gelbe Seiten etc.).

Ist also nicht immer nur die schon fast sprichwörtliche Dummheit oder Unerfahrenheit des Programmierers, die zu Datenbankfehlern führen kann ...

[Olli73]

Zitat:

Zitat von Perlsau (Beitrag 1313929)

Zitat:

Zitat von mkinzler (Beitrag 1313921)

Da rächt sich die Ansicht, dass man man aus Zeit-/Kostengründen beim Datenbankdesign auf die Deklaration der Constraints VWzüchten kann, da man diese ja im Programm berücksichtigt.

Oh, da bist du aber leicht im Irrtum: Selbstverständlich existiert ein eindeutiger Index Ortname-PLZ, so daß dieselbe Variante nicht doppelt vorkommen kann.

Da war wohl was anderes gemeint: Du kannst in der Datenbank den Foreign Key / Constraint so hinterlegen, dass die DB es nicht zugelassen hätte, einen Ort zu Löschen, solange dieser noch in einem Adress-Datensatz verwendet wird. Der "fehlerhafte Datensatz" wäre dir also schon beim Lösch-Versuch der "überflüssigen" Orte aufgefallen und nicht erst später. Es wäre also nie zu einer Inkonsistenz gekommen.

Als weiterer Hinweis: Ist die ORT_ID in der Tabelle Adressen als "NOT NULL" (also als Pflichtfeld) deklariert? Falls nicht, solltest du einen "left outer join" (ähnlich wie in #3) in deine View einbauen, damit dort auch Adressen auftauchen, für die kein Ort angegeben wurde.

[Perlsau]

Ach, jetzt versteh ich: Klar, die Ort-Id in der Adresstabelle als ForeignKey zu kennzeichnen ist sinnvoll. Hab das soeben nachgeholt:

Code:

alter table ADRESSEN
add constraint FK_ADRESSEN_ORTE
foreign key (ORT) references ORTE(ID_ORTE)
using desc index IX_ADRESSEN_ORTE

Zitat:

Zitat von Olli73 (Beitrag 1313931)

Als weiterer Hinweis: Ist die ORT_ID in der Tabelle Adressen als "NOT NULL" (also als Pflichtfeld) deklariert?

Selbstverständlich: PK ist bei mir in der Regel immer NOT NULL, weil ich den mit dem AutoInc-Tool von IbExpert erstelle; der macht das automatisch.

[Olli73]

Zitat:

Zitat von Perlsau (Beitrag 1313932)

Zitat:

Zitat von Olli73 (Beitrag 1313931)

Als weiterer Hinweis: Ist die ORT_ID in der Tabelle Adressen als "NOT NULL" (also als Pflichtfeld) deklariert?

Selbstverständlich: PK ist bei mir in der Regel immer NOT NULL, weil ich den mit dem AutoInc-Tool von IbExpert erstelle; der macht das automatisch.

Ich meinte eigentlich nicht den PK (in Tabelle Orte) sondern den FK (auf ORT_ID, in Tabelle Adressen). Kurz gesagt: Lässt es die DB zu, dass du eine Adresse speicherst und dabei die ORT_ID auf null setzt.

[Perlsau]

Da oben steht doch schwarz auf weiß: ALTER TABLE ADRESSEN
Wie kommst du jetzt drauf, ich hätte die Tabelle Orte geändert? Nach dem oben geposteten SQL-Script habe ich nun einen Foreign-Key in der Tabelle Adressen, der auf den den PK der Tabelle ORTE zeigt. Wo siehst du da jetzt ein Problem?

[DeddyH]

Genau das war doch die Frage: ist es möglich, dass das Foreign-Key-Feld auf Orte in der Adress-Tabelle NULL enthält? Übrigens sind in allen mir bekannten DBMs PK immer NOT NULL und UNIQUE, ohne dass man das explizit angeben müsste. Ohne das könnten sie ihren Zweck ja auch kaum erfüllen.

[jobo]

Zitat:

Zitat von Perlsau (Beitrag 1313935)

Da oben steht doch schwarz auf weiß: ALTER TABLE ADRESSEN
Wie kommst du jetzt drauf, ich hätte die Tabelle Orte geändert?

Darauf kommt er nicht, er fragt nach, ob in der Adresstabelle der Schlüssel auf den Ort null sein darf. Das wäre u.U. eine weitere Quelle für Inkonsistenzen.

[Hansa]

Zitat:

Zitat von Perlsau (Beitrag 1313932)

Ach, jetzt versteh ich: Klar, die Ort-Id in der Adresstabelle als ForeignKey zu kennzeichnen ist sinnvoll.

Vielleicht ist das jetzt halb klar. Der Grund wohl noch nicht richtig. Die Antworten sind mir allerdings zu kurz. Du musst prinzipiell dafür sorgen, dass keine Datenleichen im Keller der DB rumliegen. Das passiert aber ohne die Foreign Keys vor allem ohne Regeln. Von letzterem sehe ich immer noch nichts. Plz/Schreibweise Ortsname usw. ist mir jetzt zu undurchsichtig. Deshalb folgendes Beispiel : ich will einen Lieferanten löschen, was nun ? Ich lösche den einfach und fertig. Jetzt habe ich aber auch 10 Artikel, die dieser Lieferant liefert. Auch egal, dann werden die eben auch gelöscht. Geht sogar ohne eigene Programmierung in der DB über

Code:

on delete cascade

bei den foreign keys. Lieferant löschen => auch Artikel sind weg, sofern die einen foreign Key auf die Lieferanten-ID haben.

Jetzt gehts aber irgendwann ums Geld : da sind nämlich noch 2 unbezahlte Rechnungen mit den 10 Artikeln, die ich ja soeben gelöscht habe wegen des Lieferanten. Schreib jetzt mal davon eine Mahnung. Die würde ähnlich aussehen, wie Deine Liste. Wegen der gelöschten Artikel stehen keine Rechnungspositionen auf der Rechnung, eventuell aber ein Endbetrag, weil der mit dem gelöschten Kram prinzipiell nichts zu tun hat. Also, das ganze ist auch ein Rattenschwanz bei dem man verdammt aufpassen muss.

Um die verschwundenen Rechnungs-Artikel eventuell noch irgendwie sichtbar zu halten gibts ja noch mehr :

Code:

on delete set null

z.B. oder

Code:

on update cascade

Unbedingt ansehen ! Einfach löschen geht jedenfalls nicht. Bei abhängigen Tabellen komplett auf Foreign Keys zu verzichten, tsts. 8-)

[mensch72]

Ihr geht bei euren Erklärungen aus meiner Sicht etwas zu sehr in die (SQL)Details der Realisierung.

-> Hier geht es doch schlicht um die (derzeit dort wohl fehlende) "Referenzielle Integrität", was für mich die Hauptaufgabe eines DBMS ist, denn Daten in Tabellen speichern&abfragen, das kann man notfalls auch per CSV Dateien.


Ein "on delete cascade" wird es in meinen Anwendungen nicht geben. Dort sollen die Exceptions fliegen, wenn wer was zu löschen versucht, was noch anderswo in Benutzung ist.
Wenn überhaupt mache ich das per Triggerfunktion, wo dann StepByStep mit voller Absicht alles einzeln gelöscht wird/werden muss.


Ein "on update cascade" ist bei mir das einzige, was ich "notfalls" im SingeUserMode für Admins zulasse. Es passiert im Alltag, das mal Artikelnummern "auf die schnelle" von irgendwem angelegt werden, aber derjenige übersieht das es eigentlich eine Struktur gibt, wo sagen wir 1xxxyyyyyy doch was anderes ist wie 2xxxyyyyyy... Anwender sind manchmal kreativ beim einbauen und erfinden von eigenen Zusatzregeln... um sowas dann "in einem Rutsch" zu ändern, so das es sich automatisch auf die gesamte DB auswirkt, egal wo etwas mit diesem Datensatz zu tun hat, genau dafür ist dann ein "on update cascade" brauchbar, aber nicht als Standard. Per Default lasse ich das DBMS eine Execption schmeißen, wenn jemand versucht "Verknüpfungsdaten" einfach so zu ändern.


Das mal als einfache "Erklärung" ohne viel DBMS/SQL Speak, denn ich gebe zu, das ich sowas per Datenbankdesigner-Software und nicht per manuellem SQL Script erstelle und konfiguriere... Hauptsache ich weiß, warum ich es tue:)
(ps: so Sachen wie unterschiedliche Schreibweisen funktionieren bei mir mit zusätzlichen Alias-Tabellen in diesem Fall z.B. über PLZ+Vorwahl als Zusatzschlüssel)

[Perlsau]

Zitat:

Zitat von mensch72 (Beitrag 1313940)

Ein "on delete cascade" wird es in meinen Anwendungen nicht geben. Dort sollen die Exceptions fliegen, wenn wer was zu löschen versucht, was noch anderswo in Benutzung ist. Wenn überhaupt mache ich das per Triggerfunktion, wo dann StepByStep mit voller Absicht alles einzeln gelöscht wird/werden muss.

In der Anwendung, um die es hier ging, erscheint eine Meldung, wenn der Anwender z.B. einen Ort zu löschen versucht, der bereits mit einer Adresse verknüpft ist: "Dieser Ort kann nicht gelöscht werden, weil derzeit 5 Adressen mit diesem Ort existieren."

Wie oben bereits berichtet, entstanden durch das Einlesen von "unordentlichen" Listen Orte mit unterschiedlichen Schreibweisen, aber gleichen Postleitzahlen. Das ließe sich nur vermeiden, wenn man die Listen vor dem Einlesen manuell am Editor durchforstet, und das wäre ja wohl der Gipfel der Zeitverschwendung ... und der zermürbenden Arbeit ...
Diese Listen müssen von mir nur einmal eingelesen werden, der Anwender bearbeitet die Datenbank-Einträge dann per Hand ... so viele neue Firmen werden in dieser Branche nicht ständig gegründet und beim Anwender angemeldet, daß er das nicht manuell bewältigen könnte. So kann er dann prüfen, ob der Eintrag korrekt ist, die Firmendaten stimmen usw. Die Listen, die ich einmalig einlese und oberflächlich verifiziere, sind dagegen in einem Zeitraum von zig Jahren entstanden.

Zitat:

Zitat von mensch72 (Beitrag 1313940)

Ein "on update cascade" ist bei mir das einzige, was ich "notfalls" im SingeUserMode für Admins zulasse. Es passiert im Alltag, das mal Artikelnummern "auf die schnelle" von irgendwem angelegt werden, aber derjenige übersieht das es eigentlich eine Struktur gibt, wo sagen wir 1xxxyyyyyy doch was anderes ist wie 2xxxyyyyyy... Anwender sind manchmal kreativ beim einbauen und erfinden von eigenen Zusatzregeln... um sowas dann "in einem Rutsch" zu ändern, so das es sich automatisch auf die gesamte DB auswirkt, egal wo etwas mit diesem Datensatz zu tun hat, genau dafür ist dann ein "on update cascade" brauchbar, aber nicht als Standard. Per Default lasse ich das DBMS eine Execption schmeißen, wenn jemand versucht "Verknüpfungsdaten" einfach so zu ändern.

Genau so macht man das :thumb:

Daß das Thema hier trotz bereits erfolgter Problemlösung ständig weitere Kommentare erhält, die meinem Empfinden nach einer teilweise fragwürdigen Intention geschuldet sind, hat mich ein wenig verwundert ... nur ein wenig deshalb, weil das hier im Grunde täglich zu beobachten ist.

[Dejan Vu]

Was hier versucht wurde, nennt sich 'Recording Linking' oder einfacher 'Deduplizierung'. Eigentlich ist der Prozess viel komplexer, Perlsau hat die Vorarbeit implizit durch die Orte-Tabelle schon geleistet.

Hier wäre folgende Vorgehensweise sinnvoll gewesen:
Im ersten Schritt werden Duplikate erkannt und verknüpft. z.B. über eine Link-Tabelle. Im zweiten Schritt kann man die Duplikat-IDs in der referenzierenden Tabelle durch die Original-ID ersetzen. Im letzten Schritt können dann die Duplikate sicher entsorgt werden.

Natürlich ersetzt das nicht die notwendige Sicherstellung der referenziellen Integrität durch entsprechende Constraints.

[Hansa]

Zitat:

Zitat von Perlsau (Beitrag 1313943)

Daß das Thema hier trotz bereits erfolgter Problemlösung

Wo ist das Problem denn gelöst ? Im Nirwana ! Es sei denn für einmalige Aktion. Rumgefummel an Datenbank.

Nun denn, dann eben One-Man-Show und Perlsau ist kurz glücklich bis zum nächsten Crash.

Zitat:

1xxxyyyyyy doch was anderes ist wie 2xxxyyyyyy... Anwender sind manchmal kreativ beim einbauen und erfinden von eigenen Zusatzregeln... um sowas dann "in einem Rutsch" zu ändern, so das es sich automatisch auf die gesamte DB auswirkt, egal wo etwas mit diesem Datensatz zu tun hat, genau dafür ist dann ein "on update cascade" brauchbar, aber nicht als Standard. Per Default lasse ich das DBMS eine Execption schmeißen, wenn jemand versucht "Verknüpfungsdaten" einfach so zu ändern.

Man nehme zum kompletten Datensalat dann noch die sogenannte "sprechende Artikelnummer", wie Mensch72 anführt. Im Textilbereich immer noch verbreitet. D.h. Ich packe alle Informationen, die ich brauche in eine Artikelnummer, anstatt in Datensatz-Felder (Standard ca 1950er Jahre). Die angesprochenen xxx in 1xxxyyyyyy steht dann z.B. für Textilgrösse 036. yyyyy z.B. für "rot" und dann lässt man den Enduser da rumwurschteln nach Belieben. Die EAN/Art.-Nr., z.B. 1234567890123 steht natürlich auch noch drin. Und fertig. Dann sind für ein 5 EUR T-shirt 20-30 Zeichen zur Warenerfassung nötig. :shock: Aber die führenden Nullen für die Grösse nicht vergessen ! Oder dem Programmierer mitteilen, dass meistens die führende 0 vergessen wird ! Na denn viel Vergnügen. :mrgreen:

[Dejan Vu]

Das Problem ist imho dadurch gelöst, das:
1. Ein Foreign Key Constraint angelegt wurde bzw. beim nächsten Mal angelegt wird.
2. Auf 'Record Linkage' und 'Deduplication' verwiesen wurde, da es sich um ein Standardproblem handelt.

Desweiteren geht es hier nicht um das Anti-Pattern 'Sprechende eindeutige Werte sind gleichzeitig PK'. Nebenbei: Man kann in seiner Artikelnummer soviel kodieren, wie man möchte, nur darf man diese NIE NIE NIEMALS als PK verwenden. Als PK eignet sich !nur! eine anonyme ID, die keine Sau interessiert, die man nie sieht, über dessen Aussehen sich niemand aufregt und die einfach nur eine ID ist. Mehr nicht. Ein AutoInc ist dafür geeignet, oder eine GUID oder was weiß ich. Aber *KEINE* Artikelnummer.