MySQL CHECK Constraints ab Version 8.0.16 verwenden
Seit der Version 8.0.16 unterstützt der MySQL-Datenbankserver die Nutzung von CHECK-Constraints. Mit dieser Funktion lassen sich Bedingungen festlegen, die beim Einfügen oder Aktualisieren von Daten geprüft werden. Wird die festgelegte Bedingung nicht erfüllt, gibt MySQL einen allgemeinen Fehler aus und bricht die Verarbeitung der SQL-Anweisung ab. Mithilfe von CHECK-Constraints können Sie Geschäftsregeln auf Datenbankebene umsetzen, um die Datenkonsistenz Ihrer Anwendung sicherzustellen. So stellen Sie sicher, dass auch Nutzer, die direkt in der Datenbank arbeiten und keine Benutzeroberfläche verwenden, den definierten Vorgaben folgen.
Ein typisches Beispiel: Angenommen, alle Mitarbeitenden sollen ausschließlich aus dem Bundesstaat CALIFORNIA stammen. Um sicherzustellen, dass diese Regel nicht verletzt wird, lässt sich eine Validierungslogik direkt in der Datenbank integrieren. Wenn ein Nutzer versucht, einen Mitarbeitenden aus einem anderen Bundesstaat einzutragen, wird ein Fehler ausgelöst. Diese Anleitung zeigt, wie Sie eine sample_company-Datenbank und eine employees-Tabelle anlegen sowie ein CHECK-Constraint auf das Feld state anwenden, um Eingaben auf erlaubte Werte zu begrenzen.
Voraussetzungen
Um das Beispiel erfolgreich nachzuvollziehen, benötigen Sie folgende Komponenten:
- Einen Server mit installiertem Ubuntu 20.04
- Zugriff auf einen Benutzer mit sudo-Rechten
- Eine installierte LAMP-Umgebung
Datenbank sample_company erstellen
Verbinden Sie sich zunächst mit Ihrem Server und melden Sie sich mit Root-Rechten bei MySQL an:
$ sudo mysql -u root -p
Geben Sie anschließend das Root-Passwort Ihres MySQL-Servers ein und bestätigen Sie mit Enter. Führen Sie danach folgenden Befehl aus, um die Datenbank sample_company anzulegen:
mysql> CREATE DATABASE sample_company;
Wechseln Sie nun in die soeben erstellte Datenbank mit diesem Befehl:
mysql> USE sample_company;
Die Tabelle employees definieren
Im nächsten Schritt legst du eine employees-Tabelle an. Diese dient als Verzeichnis für neue Mitarbeitende. Jeder Datensatz steht für eine Person und erhält eine eindeutige employee_id als Identifikationsmerkmal. In den Feldern first_name und last_name werden Vor- und Nachnamen gespeichert. Das Feld state gibt an, aus welchem Bundesstaat die jeweilige Person stammt.
Verwende den folgenden Befehl, um die Tabellenstruktur anzulegen:
mysql> CREATE TABLE employees (
employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
state VARCHAR(50)
) ENGINE = InnoDB;
Bevor du Datensätze in die Tabelle einfügst, definierst du im nächsten Schritt eine Validierungsregel.
CHECK-Constraint auf zulässige Bundesstaaten anwenden
Nachdem die Tabelle employees nun vorhanden ist, legst du ein CHECK-Constraint fest, das nur Einträge aus dem Bundesstaat CALIFORNIA erlaubt.
Nutze den folgenden SQL-Befehl, um diese Einschränkung umzusetzen:
mysql> ALTER TABLE employees
ADD CHECK (state = 'CALIFORNIA');
Um zu prüfen, ob das Constraint korrekt hinzugefügt wurde, sollte folgende Ausgabe erscheinen:
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
CHECK-Constraint-Funktion überprüfen
Um sicherzustellen, dass das CHECK-Constraint auf die Spalte state korrekt funktioniert, beginne damit, einen gültigen Datensatz in die employees-Tabelle einzufügen. Achte dabei darauf, dass der Wert in der Spalte state der festgelegten Bedingung entspricht.
Nutze folgenden SQL-Befehl, um einen gültigen Eintrag hinzuzufügen:
mysql> INSERT INTO employees
(
first_name,
last_name,
state
)
VALUES
(
'JOHN',
'DOE',
'CALIFORNIA'
);
Dieser Eintrag sollte ohne Fehler in die employees-Tabelle übernommen werden.
Query OK, 1 row affected (0.00 sec)
Um zu prüfen, ob der Datensatz korrekt gespeichert wurde, führe folgende Abfrage aus:
mysql> SELECT
employee_id,
first_name,
last_name,
state
FROM employees;
Wenn der Eintrag erfolgreich war, sollte die Ausgabe wie folgt aussehen:
+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | state |
+-------------+------------+-----------+------------+
| 1 | JOHN | DOE | CALIFORNIA |
+-------------+------------+-----------+------------+
1 row in set (0.00 sec)
Im nächsten Schritt versuchst du, einen Mitarbeitenden aus einem nicht zugelassenen Bundesstaat – in diesem Fall FLORIDA – einzufügen. Damit testest du, ob das CHECK-Constraint unerlaubte Einträge korrekt blockiert.
Führe den folgenden SQL-Befehl aus, um den ungültigen Eintrag zu testen:
mysql> INSERT INTO employees
(
first_name,
last_name,
state
)
VALUES
(
'MARY',
'SMITH',
'FLORIDA'
);
Dieser Eintrag sollte fehlschlagen, und MySQL wird eine Fehlermeldung anzeigen, die auf die Verletzung des Constraints hinweist:
ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.
Mehrere zulässige Werte mit CHECK-Constraint in MySQL definieren
Um deine Validierungslogik zu erweitern, kannst du mit der IN-Klausel in MySQL eine Liste zulässiger Bundesstaaten definieren. Damit erlaubt die employees-Tabelle nur noch Einträge aus einer festgelegten Auswahl an Bundesstaaten.
Zunächst musst du das bestehende CHECK-Constraint entfernen, bevor du ein neues anlegst. Ermittle dazu zunächst den Namen des vorhandenen Constraints über die folgende Abfrage an die INFORMATION_SCHEMA:
mysql> SELECT
CONSTRAINT_NAME,
TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'employees'
AND CONSTRAINT_SCHEMA = 'sample_company'
AND CONSTRAINT_TYPE = 'CHECK';
In der Ausgabe findest du unter CONSTRAINT_NAME den Namen des Constraints:
+-----------------+----------------+------------+
| CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME |
+-----------------+----------------+------------+
| employees_chk_1 | sample_company | employees |
+-----------------+----------------+------------+
1 row in set (0.01 sec)
Entferne das bestehende Constraint mit folgendem Befehl:
mysql> ALTER TABLE employees
DROP CHECK employees_chk_1;
Die erfolgreiche Entfernung wird durch diese Rückmeldung bestätigt:
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Definiere nun ein neues CHECK-Constraint, das nur Einträge aus CALIFORNIA, COLORADO und ARIZONA erlaubt:
mysql> ALTER TABLE employees
ADD CHECK (state IN ('CALIFORNIA', 'COLORADO', 'ARIZONA'));
Nach Ausführung dieses Befehls solltest du folgende Bestätigung erhalten:
Query OK, 1 row affected (0.23 sec)
Records: 1 Duplicates: 0 Warnings: 0
Teste das neue Constraint, indem du Mitarbeitende aus COLORADO und ARIZONA einfügst:
mysql> INSERT INTO employees
(
first_name,
last_name,
state
)
VALUES
(
'JANE',
'MARK',
'COLORADO'
);
mysql> INSERT INTO employees
(
first_name,
last_name,
state
)
VALUES
(
'PETER',
'SMITH',
'ARIZONA'
);
Wenn die Einträge erfolgreich sind, erhältst du folgende Rückmeldung:
Query OK, 1 row affected (0.00 sec)
Überprüfe nun mit einer SELECT-Abfrage, ob alle drei Mitarbeitenden korrekt gespeichert wurden:
mysql> SELECT
employee_id,
first_name,
last_name,
state
FROM employees;
Die Ausgabe sollte nun wie folgt aussehen:
+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | state |
+-------------+------------+-----------+------------+
| 1 | JOHN | DOE | CALIFORNIA |
| 2 | JANE | MARK | COLORADO |
| 3 | PETER | SMITH | ARIZONA |
+-------------+------------+-----------+------------+
3 rows in set (0.00 sec)
Zum Abschluss teste das Constraint erneut, indem du versuchst, einen Mitarbeitenden aus einem nicht erlaubten Bundesstaat – zum Beispiel HAWAII – hinzuzufügen:
mysql> INSERT INTO employees
(
first_name,
last_name,
state
)
VALUES
(
'FRANK',
'JACOB',
'HAWAII'
);
Dieser Eintrag sollte fehlschlagen, und du bekommst folgende Fehlermeldung:
ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.
Zusammenfassung
In dieser Anleitung hast du gelernt, wie man in MySQL CHECK-Constraints verwendet, um die Datenintegrität zu verbessern. Mit dieser Methode kannst du sicherstellen, dass nur gültige Werte in deine Datenbank gelangen – ein wichtiger Schritt zur Einhaltung deiner Geschäftsregeln auf Datenbankebene.
