MariaDB-Kurs / Eingaben mit NULL, DEFAULT, NOT NULL und CHECK festlegen
NULL steht für fehlende oder unbekannte Informationen und unterscheidet sich von null und einer leeren Zeichenfolge. DEFAULT liefert einen Wert bei ausgelassener Eingabe, NOT NULL weist NULL zurück und CHECK prüft eine Bedingung. Kombinieren Sie diese Regeln, weil jede ein anderes Prüfproblem löst.
NULL, leeren Text, null und Auslassen unterscheiden
| Eingabezustand | Bedeutung | Beispiel |
|---|---|---|
| NULL | Fehlend, unbekannt oder noch nicht bestimmt | Eine Notiz wurde noch nicht erfasst |
| Leere Zeichenfolge '' | Bekannter Text der Länge null | Eine Notiz wurde ohne Text übermittelt |
| 0 | Der Zahlenwert null | Keine Einheit auf Lager |
| Ausgelassene Spalte | Nicht in der Spaltenliste von INSERT enthalten | Der Bestandsvorgabewert liefert null |
Ein Unternehmen kann eine nicht erfasste und eine leere Notiz gleich behandeln. Das ist jedoch eine Datenregel. Wer jedes NULL automatisch durch leeren Text ersetzt, verliert diese Unterscheidung. Die Übung verwendet gewöhnliche Textbehandlung ohne Kompatibilitätsmodi wie EMPTY_STRING_IS_NULL.
Die meisten gewöhnlichen Vergleiche mit NULL liefern ein unbekanntes Ergebnis, dargestellt als NULL, statt wahr oder falsch. Prüfen Sie mit IS NULL und nicht mit = NULL. Die Referenz zu NULL-Werten erläutert Operatoren und Ausnahmen.
Jeder Regel eine eigene Aufgabe zuordnen
| Definition | Wirkung | Keine Garantie für |
|---|---|---|
| DEFAULT 0 | Liefert null, wenn die Spalte ausgelassen oder DEFAULT angefordert wird | Ersetzt nicht allgemein jedes ausdrücklich angegebene NULL |
| NOT NULL | Weist die Kennzeichnung für fehlende Werte zurück | Verbietet nicht leeren Text, null oder jede negative Zahl |
| CHECK (stock >= 0) | Weist eine falsche Bedingung für nicht negativen Bestand zurück | Verhindert allein kein NULL in einer dafür offenen Spalte |
| NOT NULL mit CHECK | Verlangt einen Wert und prüft die Bedingung | Ersetzt weder Anwendungsmeldungen noch die Prüfung alter Daten |
Erfinden Sie mit Vorgabewerten keine unbekannten Informationen. Wurde der Bestand noch nicht gezählt, kann null fälschlich „ausverkauft“ bedeuten. Dieses Beispiel definiert den Anfangsbestand eines neuen Artikels bewusst als null. Ein anderer Anwendungsfall kann NULL oder einen gesonderten Prüfstatus benötigen.
Die Anwendung kann verständliche Meldungen anbieten; Datenbankregeln schützen die Speicherung, wenn ein anderes Programm, ein Client oder eine SQL-Datei schreibt. Nur das sichtbare Formular zu prüfen, kontrolliert nicht alle Schreibwege.
Das vollständige Beispiel für Vorgaben und fehlende Werte ausführen
Verbinden Sie sich als course_user mit verfügbarem shopcourse gemäß der Anleitung zu Client und SQL-Dateien. Führen Sie die Prüfung innerhalb der Verbindung aus:
SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;
Die ausgewählte Datenbank soll shopcourse sein. Prüfen Sie, dass validation_lab und nullable_check_lab noch nicht existieren. Andernfalls untersuchen Sie die Objekte vor einer erneuten Erstellung. Speichern Sie das vollständige normale Beispiel als UTF-8-Datei und verwenden Sie SOURCE mit ihrem tatsächlichen lokalen Pfad. Halten Sie absichtliche Fehler getrennt.
Die Datei fügt ausschließlich gültige Zeilen ein. Sie speichert SQL-Modus und CHECK-Auswertung der Sitzung und aktiviert danach ausdrücklich den strikten Modus und die Prüfungen. Die CHECK-Unterstützung wurde mit MariaDB 10.2.1 eingeführt. Bei alten Installationen darf das Akzeptieren einer Deklaration nicht mit dem Nachweis ihrer Durchsetzung verwechselt werden. Prüfen Sie die tatsächliche Version.
USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET @saved_check_constraints = @@SESSION.check_constraint_checks;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION check_constraint_checks = ON;
CREATE TABLE validation_lab (
item_id INT PRIMARY KEY,
item_name VARCHAR(100) NOT NULL,
note VARCHAR(100) NULL DEFAULT NULL,
stock INT NOT NULL DEFAULT 0,
CONSTRAINT ck_validation_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO validation_lab (item_id, item_name, note) VALUES
(1, 'Notebook', NULL),
(2, 'Mug', '');
SELECT item_id, note IS NULL AS note_is_null,
note = '' AS note_is_empty, stock
FROM validation_lab ORDER BY item_id;
CREATE TABLE nullable_check_lab (
item_id INT PRIMARY KEY,
stock INT NULL,
CONSTRAINT ck_nullable_stock CHECK (stock >= 0)
) ENGINE=InnoDB;
INSERT INTO nullable_check_lab VALUES (1, NULL);
SELECT item_id, stock IS NULL AS stock_is_null FROM nullable_check_lab;
| Zeile | note_is_null | note_is_empty | stock |
|---|---|---|---|
| 1 | 1 | NULL | 0 |
| 2 | 0 | 1 | 0 |
Die erste Notiz ist NULL; deshalb ist auch ihr Vergleich mit leerem Text NULL. Die zweite Notiz ist leer: IS NULL ergibt falsch, der Vergleich mit '' wahr. Beide Einfügungen lassen stock aus und erhalten 0. Die unveränderten SQL-Texte Notebook und Mug bedeuten Notizbuch und Becher.
Die separate Tabelle nullable_check_lab akzeptiert einen NULL-Bestand. Ihr CHECK-Ausdruck ergibt für NULL nicht falsch. Wenn der Bestand stets bekannt und nicht negativ sein muss, deklarieren Sie zusätzlich NOT NULL. Der Vergleich auf nicht negativen Bestand reicht allein nicht aus.
Fehler von NOT NULL und CHECK getrennt testen
Führen Sie nach dem gültigen Beispiel jeweils eine Anweisung aus. Die erste liefert NULL für den verpflichtenden Namen. Die zweite liefert einen Namen, verletzt aber die Bedingung für einen nicht negativen Bestand:
INSERT INTO validation_lab (item_id, item_name) VALUES (3, NULL);
INSERT INTO validation_lab (item_id, item_name, stock) VALUES (3, 'Desk lamp', -1);
Erwartet wird zuerst ein Fehler für einen unzulässigen NULL-Wert und danach ein Fehler, der die Bestandsregel nennt. Nach jedem Fehler soll SELECT COUNT(*) FROM validation_lab weiterhin zwei melden. Die Kennung 3 wurde beim ersten Fehler nicht gespeichert und kann für den zweiten Test erneut verwendet werden. Der Wortlaut hängt von Version und Ausgabesprache ab. Identifizieren Sie die fehlerhafte Spalte oder Regel.
Vorgabe, Eingabeform und Typregeln bestimmen, ob ein ausdrückliches NULL erlaubt, abgelehnt oder besonders behandelt wird. AUTO_INCREMENT und TIMESTAMP haben in bestimmten Fällen besonderes NULL-Verhalten. Verallgemeinern Sie dieses Beispiel mit Notiz und Bestand deshalb nicht auf jeden Typ.
Leere Werte, Regelnamen und Durchsetzung prüfen
Ein NOT-NULL-Text kann weiterhin leer sein, wie die Anleitung zu Zeichenfolgentypen erklärt. Eine Regel wie CHECK (CHAR_LENGTH(TRIM(item_name)) > 0) kann zusätzlich leere oder nur aus gewöhnlichen Leerzeichen bestehende Namen abweisen. Die Standardfunktion TRIM entfernt nicht zwangsläufig jedes Unicode-Leerzeichen oder unsichtbare Zeichen.
SHOW CREATE TABLE validation_lab;
SELECT @@SESSION.sql_mode, @@SESSION.check_constraint_checks;
Der Name ck_validation_stock hilft, eine fehlgeschlagene Regel zu finden. Verwenden Sie aussagekräftige Namen für Tabelle, Spalte oder Bedingung und prüfen Sie deren Geltungsbereich bei verschiedenen Serverversionen. Die Referenz zu Einschränkungen dokumentiert CHECK-Syntax und Durchsetzungssteuerung.
Das Deaktivieren von check_constraint_checks kann Werte zulassen, die den deklarierten Regeln widersprechen. Korrigieren Sie Eingabe oder beabsichtigte Regel, statt die Prüfung nur für eine erfolgreiche Speicherung abzuschalten. Das erneute Aktivieren bedeutet auch nicht, dass alle früher gespeicherten ungültigen Zeilen geprüft oder repariert wurden.
Einstellungen wiederherstellen und nur die Übung zurücksetzen
SET SESSION check_constraint_checks = @saved_check_constraints;
SET SESSION sql_mode = @saved_sql_mode;
Stellen Sie die Einstellungen in derselben Verbindung wieder her, in der die Variablen gespeichert wurden. Nach dem Trennen enthalten sie die ursprünglichen Werte nicht mehr. Prüfen Sie dann die neue Sitzung, statt leere Variablen anzuwenden.
Prüfen Sie vor einer Wiederholung SELECT DATABASE() und SHOW CREATE TABLE für jede der beiden Tabellen. Bestätigen Sie, dass es die entbehrlichen Objekte aus dieser Übung sind, und sichern Sie benötigte Daten. Entfernen Sie die Ziele erst danach:
DROP TABLE shopcourse.validation_lab, shopcourse.nullable_check_lab;
DROP TABLE entfernt die genannten Tabellen und ihre Zeilen und wird nicht durch ein gewöhnliches ROLLBACK rückgängig gemacht. Danach können Sie das normale Beispiel erneut ausführen. Die Tabelle products aus der ersten Produktübung bleibt von diesem Zurücksetzen ausgenommen.




