Language: Deutsch English















Last Update: 2024 - 01 - 25








Gültigkeitsregeln in Microsoft Access Tabellen

by Philipp Stiefel, ursprünglich veröffentlicht am 27. Dezember 2017
zuletzt aktualisiert 2021-01-12


Approved checklist, article header image

Based on a photo by Glenn Carstens-Peters, used here under CC0 licensing

Sie werden oft übersehen, dennoch sind sie ein wichtiger Teil des Datenbankentwurfs. - Gültigkeitsregeln in Access Tabellen. – Auch bekannt als CHECK CONSTRAINTs im SQL Standard und in den meisten anderen Datenbanksystemen.

Warum überhaupt Gültigkeitsregeln in Tabellen verwenden?

In Access Formularen kannst du eine Vielzahl von Benutzerdefinierten Datenvalidierungsregeln für die vom Benutzer in dem jeweiligen Formular eingegebenen Daten implementieren. Wenn du Before-Update (oder -Insert, -Delete) Ereignisprozeduren der einzelnen Steuerelemente oder des Formulars selbst verwendest, sind den Möglichkeiten der Eingabevalidierung praktisch keine Grenzen gesetzt.

Verglichen mit der Eingabevalidierung in einem Access Formular, haben Gültigkeitsregeln in Tabellen einen sehr eingeschränkten Umfang an möglichen Operationen. Nur eine beschränkte Untermenge der eingebauten (VBA) Funktionen ist verfügbar. Diese schließen die Domänenaggregatfunktionen,wie DomAnzahl oder DomSumme, nicht ein, die ich dabei am schmerzlichsten vermisse. Und natürlich kannst du auch keine eigenen, benutzerdefinierten VBA Funktionen in einer Gültigkeitsregel verwenden. - Dies beschränkt letztendlich jede Gültigkeitsregel darauf mit dem aktuellen Datensatz, der gerade validiert wird, zu arbeiten.

Andererseits haben aber direkt in den Tabellenentwurf integrierte Gültigkeitsregeln einen sehr bedeutenden Vorteil. Sie werden immer angewendet. Egal ob die Daten in einem bestimmen Formular eingegeben werden, durch Anfüge- oder Aktualisierungsabfragen geschrieben werden oder aus einer externen Datei importiert werden. Immer wenn Daten in einer Tabelle geändert werden, werden auch die dafür definierten Gültigkeitsregeln überprüft.

Arten von Gültigkeitsregeln

Es gibt zwei sehr ähnliche Arten von Gültigkeitsregeln, die du im Tabellenentwurf verwenden kannst. (Eigentlich gibt es drei Arten, aber dazu kommen wir später.)

Die am häufigsten verwendeten Gültigkeitsregeln sind wahrscheinlich die Feld-bezogenen Gültigkeitsregeln. In diesen Gültigkeitsregeln kannst du dich nur auf das jeweilige Feld beziehen, für das die Gültigkeitsregel gilt.

Um eine Gültigkeitsregel zu erstellen, kannst du entweder per Hand in die entsprechende Textbox der Feldeigenschaften schreiben, oder du klickst auf den […]-Button um den Ausdrucks-Generator zu starten. Ich persönlich ziehe es vor Ausdrücke per Hand zu schreiben, aber Ausdrucks-Generator hat den Vorteil, dass er nur die Funktionen zur Auswahl anbietet, die du auch wirklich im Ausdruck einer Gültigkeitsregel verwenden kannst.

Ich finde es sehr nützlich, dass man in diesen Ausdrücken auch die SQL-Vergleichsoperatoren LIKE, IN und BETWEEN verwenden kann.

Hier ist ein Beispiel für einen Ausdruck einer Gültigkeitsregel für das Feld TextCol.

Len([TextCol])>3 And Left([TextCol];1) In ("A";"B";"C")

(Das Trennzeichen (List Separator)  auf meinem Rechner ist das Semikolon (Standard für Deutschland). Wenn du das Komma als Trennzeichen eingestellt hast, solltest du Kommas anstelle der Semikola verwenden.)

Eine sehr nützliche Ergänzung zu der Gültigkeitsregel selbst, ist die Eigenschaft Gültigkeitsmeldung. Dort kannst du einen informativen Text eingeben, der dem Benutzer angezeigt wird, wenn ein Datensatz nicht gespeichert werden kann, weil er die Gültigkeitsregel verletzt.

Gültigkeitsregel fuer einzelnes Feld

Gültigkeitsregeln auf Tabellenebene sind den Gültigkeitsregeln der Felder sehr ähnlich. Der wesentliche Unterschied ist, dass du in dem Ausdruck dort jede beliebige Spalte der Tabelle referenzieren kannst.

Hier ist ein Beispiel für eine Gültigkeitsregel auf Tabellenebene. Sowohl TextCol als auch NumberCol sind Spalten in dieser Tabelle.

Len([TextCol])<=[NumberCol]

Tabellen-Gültigkeitsregel fuer mehrere Felder

Eine geringfügige Einschränkung der Tabellen-Gültigkeitsregeln ist es, dass es nur eine Gültigkeitsregel pro Tabelle geben kann. Wenn du also mehrere verschiedene Aspekte eines Datensatzes auf ihre Gültigkeit überprüfen willst, musst du die verschiedenen Prüfungen mit den AND und OR Operatoren zu einer großen Regel zusammenfassen.

Zuletzt noch ein wichtiges Detail, das du über Gültigkeitsregeln wissen solltest. Wenn ein Ausdruck zum Ergebnis NULL ausgewertet wird, dann gilt die Regel als erfüllt. - Dies ist etwas unintuitiv und du solltest es immer im Hinterkopf haben.

Es gibt viele Beispiele für Gültigkeitsregeln in dem Artikel Erstellen einer Gültigkeitsregel zum Überprüfen von Daten in einem Feld auf der Microsoft Office Webseite.

Erweiterte Check Constraints mit ADO

Mit Version 4 der Jet-Datenbank-Engine, ausgeliefert mit Access 2000 (ja, lange her), wurde eine wichtige Erweiterung eingeführt, die häufig übersehen wird. - Check Constraints.

Ein Check Constraint (wörtlich übersetzt: „Prüfbeschränkung“) ist einer Gültigkeitsregel auf Tabelleneben sehr ähnlich, aber mit einem großen Unterschied. Du kannst SQL Statements in Check Constraints verwenden. Also bist du nicht darauf beschränkt, nur den aktuellen Datensatz in einem Ausdruck zu referenzieren. Du kannst jeden beliebigen Datensatz in einer beliebigen Tabelle deiner Datenbank referenzieren. Dies schließt Werte ein, die mit SUM, COUNT oder einer anderen Aggregatfunktion ermittelt wurden.

Leider kann man für einen Check Constraint keine Gültigkeitsmeldung definieren. Der Benutzer wird also nur mit einer eher kryptischen Meldung konfrontiert, dass seine Eingabe gegen die Gültigkeitsregel verstößt.

Meldung für verletzten Check Constraint

Der Grund, dass sie häufig übersehen werden, ist dass es keine Benutzeroberfläche gibt, um Check Constraints zu verwalten. Die DAO Objektbibliothek, die standardmäßig in Access verwendet wird, erkennt die Syntax um sie zu erstellen nicht. - Wenn du sie also weder im UI noch mit einer (DAO) DDL (Data Definition Language) Abfrage erstellen kannst, wie kannst du sie dann überhaupt verwenden?

Du musst eine DDL-Abfrage über eine ADODB.Connection auf deine Access Datenbank ausführen. - Das klingt komplizierter, als es tatsächlich ist. Mit Access 2000 wurde die neue Currentproject.Connection-Eigenschaft eingeführt, die eine geöffnete ADODB.Connection auf die aktuelle Datenbank zurückliefert, ähnlich wie die CurrentDb-Methode eine geöffnete DAO.Database-Instanz zurückgibt.

Die SQL Syntax für einen Check Constraint ist:

ALTER TABLE yourTableName ADD CONSTRAINT yourNewConstraintName CHECK (yourCheckExpression);

Die Check Expression kann jeder Ausdruck sein, der in einer normalen Gültigkeitsregel (Du verwendest hier immer das Komma als Trennzeichen!) verwendet werden kann, sein. Zusätzlich kannst du aber eine beliebige SQL Abfrage verwenden, wenn diese einen einzelnen Wert zur Auswertung in den Ausdruck ergibt.

Hier ist ein Beispiel für ein Check Constraint der die Anzahl der Datensätze einer anderen Tabelle verwendet, um die Gültigkeit des aktuellen Datensatzes zu prüfen.

ALTER TABLE tblValidationRuleTest ADD CONSTRAINT chkReferenceOtherTable CHECK (Len([TextCol]) > (SELECT COUNT(*) FROM tblOtherTable));

Um dieses SQL auszuführen und den Constraint zu erstellen, verwendest du die Execute-Methode der CurrentProject.Connection. So wie hier:

With CurrentProject.Connection .Execute "ALTER TABLE tblValidationRuleTest " & _ " ADD CONSTRAINT chkReferenceOtherTable " & _ " CHECK (Len([TextCol]) > (SELECT COUNT(*) FROM tblOtherTable));" End With

Wenn du Datensätze aus einer anderen Tabelle in deinem Ausdruck verwendest, dann wird dies nur bei Änderungen an dem Datensatz in der Haupttabelle, derjenigen auf der der Constraint definiert ist, ausgewertet. Es wird nicht überprüft, wenn Änderungen an den Daten der referenzierten Tabelle vorgenommen werden.

In dem obigen Beispiel kann das Einfügen neuer Datensätze in die Tabelle tblOtherTable Datensätze in der Tabelle tblValidationRuleTest ungültig, gemessen an der Gültigkeitsregel, machen. Allerdings wird dies nicht geprüft, solange die Daten in der Tabelle tblValidationRuleTest unverändert bleiben.

Du solltest dir immer über die Performancekonsequenzen bewusst sein, die komplexe SQL Abfragen in einem Constraint haben können. Dieses SQL wird für jeden Datensatz ausgeführt, der in die Tabelle eingefügt oder geändert wird. Folglich solltest du dort nur Abfragen verwenden, die sehr schnell ausgeführt werden können.

Da es keine Benutzeroberfläche für die Check Constraints gibt, sind diese ziemlich schlecht zu entdecken, wenn du dir über ihre Existenz nicht im Klaren bist. Du kannst sie nur in der, üblicherweise ausgeblendeten, Systemtabelle MSysObjects sehen. Du kannst eine Abfrage, wie die folgende, verwenden, um alle Check Constraints anzuzeigen.

SELECT ForeignName AS TableName, Name AS ConstraintName, Connect AS CheckExpression, DateCreate, DateUpdate FROM MSysObjects WHERE Type = 9;

Check Constraints aus der MSysObjects Tabelle abgefragt

Solange auf eine Tabelle ein Constraint definiert ist, oder eine Tabelle in einem Constraint referenziert wird, kannst du diese Tabelle nicht umbenennen. Das macht natürlich Sinn. Andernfalls würdest du die Logik des Constraints zerstören. Wenn du eine Tabelle umbenennen willst, die mit einem Constraint in Zusammenhang steht, dann musst du zuerst den Constraint löschen, dann die Tabelle umbenennen und dann den Constraint mit geänderten Tabellen- oder Feldnamen neu erstellen.

Um einen Check Constraint zu löschen, musst du wieder auf ein DDL-Statement über die ADODB Connection auf die aktuelle Datenbank zurückgreifen. Die grundsätzliche SQL Syntax ist:

ALTER TABLE yourTableName DROP CONSTRAINT yourNewConstraintName;

Und hier ist eingebunden in den VBA Code, der das SQL ausführt:

With CurrentProject.Connection .Execute "ALTER TABLE tblValidationRuleTest " & _ " DROP CONSTRAINT chkReferenceOtherTable;" End With

Datenmakros

Eine weitere Möglichkeit zur Datenvalidierung sind die Datenmakros, die mit Access 2010 eingeführt wurden. Datenmakros sind ähnlich wie Trigger in server-basierenden DBMS, wie Microsoft SQL-Server. Sie enthalten prozedurale Logik, die von Datenereignissen, z.B. Einfügen, Ändern und Löschen von Daten ausgelöst wird.

Ich habe selbst nicht viel Erfahrung mit Datenmakros. Sie wären ohnehin ein Thema für einen anderen Artikel. Daher verweise ich hier bis auf weiteres auf den Microsoft-Artikel zum Erstellen eines Datenmakros.

Update: Video zu Gültigkeitsregeln und Check Constraints

Ich habe kürzlich auch ein Video zum Thema Gültigkeitsregeln und Check Constraints aufgenommen. Du findest es auf meinen YouTube-Kanal codekabinett/de und ich habe es auch hier eingebettet.

 

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Abonniere meinen Newsletter

*

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.
Die Emailliste wird bei Mailchimp in den USA gespeichert. Diese Auftragsverarbeitung ist vertraglich geregelt. Weitere Details in der Datenschutzerklärung.

Vorteile des Newsletter-Abos



© 1999 - 2024 by Philipp Stiefel - Datenschutzerklärung