In unseren Access-Audits mit unseren Kunden treffen wir immer wieder auf das folgende Problem: Es gibt Tabellen, die zwar über ein Feld Datensätze aus anderen Tabellen referenzieren, aber es wurde gar keine Beziehung für diese Zuordnung definiert. Und wenn eine Beziehung angelegt wurde, wurde für diese keine referenzielle Integrität festgelegt. Das birgt verschiedene Gefahren, die unter Umständen sogar Auswirkungen auf den Unternehmensumsatz haben. Welche das sind und wie Sie diese Probleme beheben, zeigen wir in diesem Beitrag. Die Definition von Beziehungen mit referenzieller Integrität ist essenziell und sollte, wenn diese noch nicht vorhanden sind, schnellstens nachgerüstet werden. Das funktioniert in vielen Fällen aber gar nicht so leicht, weil die Tabellen bereits inkonsistente Daten enthalten. Auch zur Identifizierung und Korrektur solcher Datensätze liefert dieser Beitrag die passenden Lösungen.
Stellen wir uns vor, wir hätten eine Tabelle zum Speichern von Bestellungen und eine für die entsprechenden Bestellpositionen. Die Tabelle der Bestellungen enthält ein Primärschlüsselfeld, welches die erste Voraussetzung für ein konsistentes Datenmodell ist – somit können alle Datensätze dieser Tabelle eindeutig identifiziert werden.
Auch die Tabelle der Bestellpositionen enthält ein solches Primärschlüsselfeld. Neben den übrigen, typischen Feldern für eine Bestellposition enthält diese auch ein Feld, mit dem wir die Bestellposition einer Bestellung zuordnen können – nennen wir es BestellungID.
Grundsätzlich ist die Zuordnung von Bestellpositionen zu einer Bestellung also gewährleistet. Aber welche Probleme können auftreten, wenn wir keine referenzielle Integrität definieren?
Probleme bei fehlender Beziehung oder ohne referenzielle Integrität
Schauen wir uns das Datenmodell aus Bild 1 an. Hier sehen wir die beiden Tabellen, anhand derer wir die Vorteile der referenziellen Integrität beschreiben wollen.
Bild 1: Tabellen der Beispieldatenbank
Der Tabelle tblBestellungen fügen wir die Datensätze aus Bild 2 hinzu.
Bild 2: Beispieldaten in der Tabelle tblBestellungen
In der Tabelle tblBestellpositionen legen wir nun eine Bestellposition für die erste Bestellung an, indem wir das Feld BestellungID auf den Wert 1 einstellen, also auf die erste Bestellung der Tabelle tblBestellungen. Soweit ist das kein Problem – die Bestellposition ist einer Bestellung zugeordnet.
Das decken wir normalerweise ab, indem wir jeweils eine Bestellung in einem Hauptformular abbilden und in einem Unterformular die Bestellpositionen. Wenn der Name des Fremdschlüsselfelds in der Tabelle des Unterformulars mit dem des Primärschlüsselfelds in der Datensatzquelle des Hauptformulars übereinstimmt, erkennt Access dies sogar beim Hinzufügen des Unterformulars und trägt dies korrekt in die Eigenschaften Verknüpfen nach und Verknüpfen von des Unterformular-Steuerelements ein (siehe Bild 3).
Bild 3: Bestellungen und Bestelldetails in Haupt- und Unterformular
Damit ordnet das Formular neue Bestellpositionen, die wir in das Unterformular eintragen, automatisch dem Bestelldatensatz im Hauptformular zu, weil das Fremdschlüsselfeld BestellungID direkt mit dem entsprechenden Primärschlüsselwert aus dem Hauptformular gefüllt wird.
Bei 99 % der ausgewerteten Anwendungen funktionierte dies nicht!
Auch wenn dies eine scheinbare Sicherheit vorgaukelt: In unseren Untersuchungen von Kundendatenbanken haben wir bei fast allen Anwendungen herausgefunden, dass dies nicht zuverlässig funktioniert. Die Gründe dafür sind nicht genauer bekannt, weil die betroffenen Datensätze oft vor langer Zeit angelegt wurden. Aber: Wir haben nahezu überall Probleme aufgedeckt, die zeigen, dass Benutzer erfinderisch sind und Wege finden, um die Zuordnung von Bestellungen und Bestellpositionen zu unterminieren.
Dies zeigte sich in den folgenden Ergebnissen:
- Entweder haben wir Datensätze in der Tabelle tblBestellpositionen gefunden, die einen Fremdschlüsselwert aufweisen, der in der Tabelle tblBestellungen nicht mehr vorhanden war (oder nie gewesen ist). Das heißt, dass entweder Bestellungen gelöscht wurden, ohne dass die entsprechenden Einträge in tblBestellpositionen auch entfernt wurden (wahrscheinlichere Variante), oder die Benutzer es geschafft haben, Fremdschlüsselwerte einzutragen, für die es kein Pendant im Primärschlüsselfeld der Tabelle tblBestellungen gab.
- Oder wir haben Einträge in der Tabelle tblBestellpositionen gefunden, die im Fremdschlüsselfeld BestellungID den Wert NULL enthielten, also leer waren.
Beides ist ungünstig, denn es wurden scheinbar einmal Bestellungen plus Bestellpositionen eingetragen, die dann nicht zur Ausführung kamen und somit potenziell den Umsatz vermindert haben.
Dies kann passieren, wenn entweder das Formular nicht sauber programmiert und mit entsprechenden Validierungen ausgestattet wurde oder wenn die Benutzer Wege gefunden haben, die Daten direkt in den zugrunde liegenden Tabellen zu manipulieren und entweder Bestelldatensätze gelöscht oder Bestellpositionen verändert haben.
Die Lösung ist also zum Beispiel, die Anwendung so sicher zu machen, dass derartige Manipulationen nicht mehr möglich sind. Dazu muss das Formular vor Fehleingaben geschützt werden und/oder man muss verhindern, dass die Benutzer direkten Zugriff auf die Tabellen erhalten und so die Daten direkt in den Tabellen manipulieren.
Dies war in den meisten Anwendungen nicht der Fall, auch wenn die Entwickler uns glaubhaft machen wollten, dass sie alle notwendigen Maßnahmen getroffen hätten.
Es gibt jedoch noch eine einfachere Möglichkeit, um sicherzustellen, dass es keine Datensätze in einer Tabelle wie tblBestellpositionen gibt, die keiner Bestellung zugeordnet sind. Dabei handelt es sich um das Anlegen einer Beziehung zwischen den Tabellen (falls bisher nicht vorhanden) und die Festlegung von referenzieller Integrität für die Beziehung.
Funktion der referenziellen Integrität
Wenn wir referenzielle Integrität definieren, aktivieren wir zwei wichtige Funktionen für die Beziehung. Nehmen wir als Beispiel wieder die Beziehung zwischen der Tabelle tblBestellungen und tblBestellpositionen. Wir fügen mit der referenziellen Integrität eine Restriktion hinzu, welche die Werte im Fremdschlüsselfeld BestellungID in der Tabelle tblBestellpositionen auf die folgenden Werte einschränkt:
- Es sind alle Werte erlaubt, die im Primärschlüsselfeld der verknüpften Tabelle vorhanden sind.
- Und zusätzlich kann, wenn wir dies nicht anderweitig unterbinden, der Wert NULL vorliegen.
Letzteres können wir verhindern, indem wir die Eigenschaft Eingabe erforderlich für das Fremdschlüsselfeld auf Ja einstellen. Das ist sinnvoll, um auch diese Lücke zu schließen. Es gibt jedoch Fälle abseits von Bestellungen und Bestellpositionen, wo man vielleicht erst die Datensätze in der Tabelle mit dem Fremdschlüsselfeld anlegt und diese erst später zuordnen möchte – dann kann man NULL-Werte zulassen.
Hier sollte man jedoch regelmäßig prüfen, ob sich keine nicht zugeordneten Datensätze in dieser Tabelle befinden.
Wenn wir keine referenzielle Integrität festlegen, können wir eine Bestellposition zur Tabelle tblBestellpositionen hinzufügen, die einen Wert im Feld BestellungID enthält, der nicht in der Tabelle tblBestellungen enthalten ist (siehe Bild 4).
Bild 4: Bestellposition ohne passende Bestellung
Wenn keine referenzielle Integrität festgelegt werden kann
Wir versuchen nun, eine Beziehung mit referenzieller Integrität für die Tabellen anzulegen. Dazu ziehen wir im Beziehungen-Fenster das Feld BestellungID der Tabelle tblBestellpositionen auf die Tabelle tblBestellungen. Es erscheint der Dialog Beziehungen bearbeiten, wo wir die Option Mit referenzieller Integrität aktivieren (siehe Bild 5).
Bild 5: Versuch, referenzielle Integrität zu definieren
Wenn wir jetzt auf Erstellen klicken, erhalten wir die Fehlermeldung aus Bild 6. Der Grund ist offensichtlich: Wir haben in der Tabelle tblBestellpositionen Werte im Feld BestellungID, die in der Tabelle tblBestellungen nicht vorhanden sind.
Bild 6: Fehlermeldung beim Versuch, referenzielle Integrität zu definieren
Dies ist der einfachste Test, um zu prüfen, ob alle Datensätze aus tblBestellpositionen korrekt der Tabelle tblBestellungen zugeordnet sind.
Etwas schwieriger wird es, im Anschluss herauszufinden, welche der Datensätze der Tabelle tblBestellpositionen das Definieren referenzieller Integrität verhindern – dazu weiter unten mehr.
Erfolgreiches Festlegen referenzieller Integrität
In diesem Fall löschen wir einfach den Datensatz, der auf die nicht vorhandene Bestellung mit dem Wert 12 im Feld BestellungID verweist. Danach können wir die referenzielle Integrität für diese Tabelle aktivieren.
Das Ergebnis sehen wir in Bild 7. Dass es sich um eine Beziehung mit referenzieller Integrität handelt, erkennen wir an dem Unendlich-Symbol auf der einen und der Pfeilspitze auf der anderen Seite.
Bild 7: Erfolgreich hergestellte referenzielle Integrität
Auswirkung der Festlegung referenzieller Integrität prüfen
Damit können wir uns nun anschauen, wie sich die referenzielle Integrität auswirkt.
Diese verhindert zunächst das Löschen von Datensätzen aus der Tabelle tblBestellungen, denen bereits ein Datensatz der Tabelle tblBestellpositionen zugeordnet wurde.
Wenn wir versuchen, eine Bestellung aus der Tabelle tblBestellungen zu löschen, der bereits eine Bestellposition zugeordnet ist, lösen wir die Fehlermeldung aus Bild 8 aus.
Bild 8: Fehler beim Versuch, eine Bestellung mit Bestellpositionen zu löschen
Auf der anderen Seite können wir in der Tabelle tblBestellpositionen keine Datensätze mehr anlegen, die im Fremdschlüsselfeld BestellungID einen Wert enthalten, der nicht im gleichnamigen Feld der Tabelle tblBestellpositionen enthalten ist.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo