SQLite Constraint Management (Constraint-Verwaltung)

Einführung

In diesem Lab werden Sie die SQLite-Constraint-Verwaltung (SQLite-Einschränkungsverwaltung) erkunden, um die Datenintegrität sicherzustellen. Sie beginnen mit der Definition von Fremdschlüssel-Constraints (Foreign Key Constraints), um Beziehungen zwischen Tabellen herzustellen, implementieren CHECK-Constraints, erstellen zusammengesetzte Schlüssel (Composite Keys) und testen schließlich Constraint-Verletzungen (Constraint Violations), um zu verstehen, wie SQLite diese Regeln durchsetzt. Diese praktische Erfahrung vermittelt Ihnen ein praktisches Verständnis dafür, wie Sie die Datenkonsistenz in Ihren SQLite-Datenbanken aufrechterhalten können.

Skills Graph

Tabellen mit einer Fremdschlüssel-Constraint (Foreign Key Constraint) erstellen

In diesem Schritt erstellen Sie zwei Tabellen, customers und orders, und richten eine Fremdschlüssel-Constraint (Foreign Key Constraint) zwischen ihnen ein. Diese Constraint stellt sicher, dass jede Bestellung einem gültigen Kunden zugeordnet ist.

Öffnen Sie zunächst die SQLite-Shell, indem Sie den folgenden Befehl in Ihrem Terminal ausführen:

sqlite3 /home/labex/project/database.db

Dieser Befehl öffnet eine Verbindung zur SQLite-Datenbankdatei namens database.db. Wenn die Datei nicht existiert, wird sie von SQLite erstellt. Sie sollten nun die sqlite>-Eingabeaufforderung sehen.

Erstellen Sie nun die Tabelle customers mit dem folgenden SQL-Befehl:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

Dieser Befehl erstellt eine Tabelle namens customers mit vier Spalten: customer_id, first_name, last_name und email. Die Spalte customer_id ist der Primärschlüssel (Primary Key) und wird für jeden neuen Kunden automatisch inkrementiert.

Erstellen Sie als Nächstes die Tabelle orders mit einer Fremdschlüssel-Constraint (Foreign Key Constraint), die auf die Tabelle customers verweist:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Dieser Befehl erstellt eine Tabelle namens orders mit vier Spalten: order_id, customer_id, order_date und total. Die Spalte order_id ist der Primärschlüssel (Primary Key). Die Klausel FOREIGN KEY (customer_id) REFERENCES customers(customer_id) richtet eine Fremdschlüssel-Constraint (Foreign Key Constraint) ein, die sicherstellt, dass die customer_id in der Tabelle orders auf eine gültige customer_id in der Tabelle customers verweist.

Um zu überprüfen, ob die Tabellen erstellt wurden, können Sie den folgenden Befehl verwenden:

.tables

Dieser Befehl listet alle Tabellen in der Datenbank auf. Sie sollten sowohl customers als auch orders in der Ausgabe sehen.

Daten einfügen und die Fremdschlüssel-Constraint (Foreign Key Constraint) testen

In diesem Schritt fügen Sie Daten in die Tabellen customers und orders ein und testen die Fremdschlüssel-Constraint (Foreign Key Constraint).

Zuerst müssen Sie Fremdschlüssel-Constraints (Foreign Key Constraints) in SQLite aktivieren (sie sind standardmäßig deaktiviert):

PRAGMA foreign_keys = ON;

Fügen Sie nun einige Daten in die Tabelle customers ein:

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]');

Dieser Befehl fügt zwei neue Kunden in die Tabelle customers ein. Die customer_id wird automatisch zugewiesen.

Fügen Sie nun eine Bestellung in die Tabelle orders ein, die auf einen der bestehenden Kunden verweist:

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

Dieser Befehl fügt eine neue Bestellung in die Tabelle orders ein und verknüpft sie mit dem Kunden, dessen customer_id 1 ist.

Versuchen Sie als Nächstes, eine Bestellung mit einer customer_id einzufügen, die in der Tabelle customers nicht existiert:

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

Sie sehen eine Fehlermeldung ähnlich dieser: Error: FOREIGN KEY constraint failed (Fehler: Fremdschlüssel-Constraint fehlgeschlagen). Dies bestätigt, dass die Fremdschlüssel-Constraint (Foreign Key Constraint) korrekt funktioniert und verhindert, dass Sie eine Bestellung für einen nicht existierenden Kunden erstellen.

Um die Daten in den Tabellen anzuzeigen, können Sie die folgenden Befehle verwenden:

SELECT * FROM customers;
SELECT * FROM orders;

Diese Befehle zeigen den Inhalt der Tabellen customers bzw. orders an.

Eine CHECK-Constraint (CHECK Constraint) implementieren

In diesem Schritt erstellen Sie eine neue Tabelle customers_with_check, die eine CHECK-Constraint (CHECK Constraint) enthält, um sicherzustellen, dass alle E-Mail-Adressen das @-Symbol enthalten.

Erstellen wir zunächst eine neue Tabelle mit der CHECK-Constraint (CHECK Constraint) direkt:

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

Dieser Befehl erstellt eine neue Tabelle namens customers_with_check mit einer CHECK-Constraint (CHECK Constraint) für die Spalte email, die sicherstellt, dass diese das @-Symbol enthält. Der LIKE-Operator wird für die Mustererkennung verwendet, und % ist ein Wildcard-Zeichen, das mit jeder Zeichenfolge übereinstimmt.

Kopieren wir nun die vorhandenen Kundendaten in die neue Tabelle:

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

Versuchen Sie nun, einen neuen Kunden mit einer ungültigen E-Mail-Adresse einzufügen:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

Sie sollten eine Fehlermeldung ähnlich dieser sehen: Error: CHECK constraint failed: email (Fehler: CHECK-Constraint fehlgeschlagen: E-Mail). Dies deutet darauf hin, dass die CHECK-Constraint (CHECK Constraint) das Einfügen der ungültigen E-Mail-Adresse verhindert hat.

Um zu überprüfen, ob die CHECK-Constraint (CHECK Constraint) funktioniert, fügen Sie einen Kunden mit einer gültigen E-Mail-Adresse ein:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', '[email protected]');

Dieser Befehl sollte erfolgreich ausgeführt werden, da die E-Mail-Adresse das @-Symbol enthält.

Eine Tabelle mit einem zusammengesetzten Schlüssel (Composite Key) erstellen

In diesem Schritt erstellen Sie eine Tabelle namens enrollments mit einem zusammengesetzten Schlüssel (Composite Key), der aus den Spalten student_id und course_id besteht.

Führen Sie die folgende SQL-Anweisung in der SQLite-Shell aus:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

Dieser Befehl erstellt eine Tabelle namens enrollments mit drei Spalten: student_id, course_id und enrollment_date. Die Klausel PRIMARY KEY (student_id, course_id) gibt an, dass der Primärschlüssel (Primary Key) für die Tabelle aus den Spalten student_id und course_id besteht. Dies bedeutet, dass die Kombination aus student_id und course_id für jede Zeile in der Tabelle eindeutig sein muss.

Fügen Sie nun einige Daten in die Tabelle enrollments ein:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

Dadurch werden drei Zeilen in die Tabelle enrollments eingefügt.

Versuchen Sie als Nächstes, eine Zeile mit derselben student_id und course_id wie eine vorhandene Zeile einzufügen:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

Sie sollten eine Fehlermeldung ähnlich dieser sehen: Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id (Fehler: UNIQUE-Constraint fehlgeschlagen: enrollments.student_id, enrollments.course_id). Dies deutet darauf hin, dass die Constraint (Constraint) für den zusammengesetzten Schlüssel (Composite Key) das Einfügen der doppelten Zeile verhindert hat.

Um zu überprüfen, ob die Tabelle korrekt erstellt wurde, können Sie den Befehl .tables in der SQLite-Shell verwenden:

.tables

Sie sollten enrollments in der Ausgabe sehen.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man Constraints (Constraints) in SQLite definiert und implementiert, um die Datenintegrität sicherzustellen. Sie haben Tabellen mit Fremdschlüssel-Constraints (Foreign Key Constraints) erstellt, CHECK-Constraints (CHECK Constraints) implementiert, um Daten zu validieren, und Tabellen mit zusammengesetzten Schlüsseln (Composite Keys) erstellt, um Zeilen anhand mehrerer Spalten eindeutig zu identifizieren. Durch das Verständnis und die Nutzung dieser Constraints (Constraints) können Sie robuste und zuverlässige Datenbanken erstellen, die die Datenkonsistenz aufrechterhalten.