aiMOOC

Datenbanken und betriebliche Datenmodelle – Integritätsregeln in Datenbanken nutzen

QR-Code

Datenbanken und betriebliche Datenmodelle – Integritätsregeln in Datenbanken nutzen

Kurzbeschreibung: Pflichtwerte, Eindeutigkeit und Referenzschutz sorgen dafür, dass betriebliche Daten auch dann konsistent bleiben, wenn Eingaben fehlerhaft sind. Du lernst die Integritätsregeln NOT NULL, UNIQUE, PRIMARY KEY und FOREIGN KEY an einem fiktiven Ausbildungsfall kennen und testest sie ausschließlich lokal mit erfundenen Daten.

Zielgruppe: Ausbildung in IT-, kaufmännischen und digitalisierungsnahen Berufen

Lernziel: Du kannst betriebliche Anforderungen in Integritätsregeln übersetzen, passende Constraints in SQL formulieren, Fehlversuche auswerten und begründen, warum die Datenbank fehlerhafte Zustände verhindert.

Einleitung

Ein betriebliches Datenmodell bildet Regeln aus der Arbeitswelt technisch ab. Nicht jede fachliche Regel sollte nur in einem Eingabeformular stehen. Wichtige Regeln gehören möglichst direkt in die Datenbank, damit sie für alle Anwendungen gelten, die auf die Daten zugreifen.

In diesem Kurs arbeitest Du mit einem fiktiven Ausbildungsbetrieb. Es werden keine Produktivsysteme, fremden Netze, echten Kundeninformationen, Zugangsdaten oder personenbezogenen Echtdaten verwendet. Alle Tests laufen lokal in einer SQLite-Datenbank im Arbeitsspeicher oder in einer selbst angelegten lokalen Datei.

Ausbildungsfall: ByteWerk Service GmbH

Die fiktive ByteWerk Service GmbH verwaltet Kunden und Aufträge.

kunden_id	kundennummer	name	email
1	K1001	Nordlicht OHG	kontakt@nordlicht.test
2	K1002	Musterwerk KG	buero@musterwerk.test

Für jeden Kunden gilt:

Pflichtwert: Kundennummer, Name und E-Mail-Adresse müssen vorhanden sein.
Eindeutigkeit: Kundennummer und E-Mail-Adresse dürfen nicht doppelt vorkommen.
Referenzschutz: Ein Auftrag darf nur auf einen vorhandenen Kunden zeigen.

Lerneinheit 1: Pflichtwerte mit NOT NULL

NOT NULL verhindert, dass in einer Spalte der Wert `NULL` gespeichert wird. Das eignet sich für Pflichtangaben, die fachlich zwingend benötigt werden.

CREATE TABLE kunde (
    kunden_id    INTEGER PRIMARY KEY,
    kundennummer TEXT NOT NULL,
    name         TEXT NOT NULL,
    email        TEXT NOT NULL
);

Ein Datensatz ohne `email` wird abgewiesen. Die Datenbank schützt damit die Regel unabhängig davon, ob die Eingabe aus einer Oberfläche, einem Import oder einem Skript kommt.

Lerneinheit 2: Eindeutigkeit mit UNIQUE

UNIQUE verhindert doppelte Werte in einer Spalte oder in einer Kombination mehrerer Spalten.

CREATE TABLE kunde (
    kunden_id    INTEGER PRIMARY KEY,
    kundennummer TEXT NOT NULL UNIQUE,
    name         TEXT NOT NULL,
    email        TEXT NOT NULL UNIQUE
);

Die Kundennummer `K1001` kann damit nur einmal gespeichert werden. Gleiches gilt hier für die E-Mail-Adresse.

Merksatz: Ein Primärschlüssel identifiziert einen Datensatz eindeutig. Zusätzliche fachlich eindeutige Merkmale können mit `UNIQUE` geschützt werden.

Lerneinheit 3: Referenzschutz mit FOREIGN KEY

Ein FOREIGN KEY verbindet eine abhängige Tabelle mit einer referenzierten Tabelle. Ein Auftrag soll nur zu einem Kunden gehören, der tatsächlich existiert.

CREATE TABLE auftrag (
    auftrag_id INTEGER PRIMARY KEY,
    kunden_id  INTEGER NOT NULL,
    betreff    TEXT NOT NULL,
    FOREIGN KEY (kunden_id)
        REFERENCES kunde(kunden_id)
        ON DELETE RESTRICT
);

`ON DELETE RESTRICT` verhindert hier, dass ein Kunde gelöscht wird, solange noch Aufträge auf ihn verweisen.

Lerneinheit 4: Die Regeln gemeinsam einsetzen

Das vollständige lokale Schema lautet:

PRAGMA foreign_keys = ON;

CREATE TABLE kunde (
    kunden_id    INTEGER PRIMARY KEY,
    kundennummer TEXT NOT NULL UNIQUE,
    name         TEXT NOT NULL,
    email        TEXT NOT NULL UNIQUE
);

CREATE TABLE auftrag (
    auftrag_id INTEGER PRIMARY KEY,
    kunden_id  INTEGER NOT NULL,
    betreff    TEXT NOT NULL,
    FOREIGN KEY (kunden_id)
        REFERENCES kunde(kunden_id)
        ON DELETE RESTRICT
);

Bei SQLite sollte die Fremdschlüsselprüfung für die verwendete Verbindung ausdrücklich mit `PRAGMA foreign_keys = ON;` aktiviert werden.

Visualisierte Daten: Was ist erlaubt?

Versuch	Beispiel	Ergebnis	Schutzregel
Gültiger Kunde	K1001, Nordlicht OHG, kontakt@nordlicht.test	erlaubt	alle Regeln erfüllt
Fehlende E-Mail	K1002, Musterwerk KG, NULL	abgewiesen	NOT NULL
Doppelte Kundennummer	K1001 erneut	abgewiesen	UNIQUE
Auftrag mit kunden_id 999	Kunde 999 existiert nicht	abgewiesen	FOREIGN KEY
Kunde mit Auftrag löschen	Auftrag verweist noch auf Kunde	abgewiesen	ON DELETE RESTRICT

Lokales Mini-Lab 1: SQLite im Arbeitsspeicher

Nutze nur eine lokale SQLite-Installation. Mit `:memory:` bleibt die Testdatenbank ausschließlich im Arbeitsspeicher und wird beim Beenden verworfen.

sqlite3 :memory:

Danach:

PRAGMA foreign_keys = ON;

CREATE TABLE kunde (
    kunden_id INTEGER PRIMARY KEY,
    kundennummer TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE auftrag (
    auftrag_id INTEGER PRIMARY KEY,
    kunden_id INTEGER NOT NULL,
    betreff TEXT NOT NULL,
    FOREIGN KEY (kunden_id)
        REFERENCES kunde(kunden_id)
        ON DELETE RESTRICT
);

INSERT INTO kunde
VALUES (1, 'K1001', 'Nordlicht OHG', 'kontakt@nordlicht.test');

INSERT INTO auftrag
VALUES (101, 1, 'Notebook einrichten');

SELECT * FROM kunde;
SELECT * FROM auftrag;

Basisaufgabe: Entferne beim nächsten Kunden die E-Mail-Adresse.

Erwartung: Der Insert wird abgewiesen.

Begründetes Feedback: Das ist korrekt, weil die Spalte `email` mit `NOT NULL` definiert ist. Die Datenbank verhindert damit einen unvollständigen Pflichtdatensatz.

Lokales Mini-Lab 2: Isolierter Python-Test

Das folgende Programm nutzt nur das Python-Standardmodul `sqlite3`, eine lokale `:memory:`-Datenbank und vollständig fiktive Daten.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")

db.executescript("""
CREATE TABLE kunde (
    kunden_id INTEGER PRIMARY KEY,
    kundennummer TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE auftrag (
    auftrag_id INTEGER PRIMARY KEY,
    kunden_id INTEGER NOT NULL,
    betreff TEXT NOT NULL,
    FOREIGN KEY (kunden_id)
        REFERENCES kunde(kunden_id)
        ON DELETE RESTRICT
);
""")

db.execute(
    "INSERT INTO kunde VALUES (?, ?, ?, ?)",
    (1, "K1001", "Nordlicht OHG", "kontakt@nordlicht.test")
)

tests = [
    (
        "NOT NULL",
        "INSERT INTO kunde(kunden_id, kundennummer, name) VALUES (?, ?, ?)",
        (2, "K1002", "Musterwerk KG")
    ),
    (
        "UNIQUE",
        "INSERT INTO kunde VALUES (?, ?, ?, ?)",
        (3, "K1001", "Doppel GmbH", "doppel@test.invalid")
    ),
    (
        "FOREIGN KEY",
        "INSERT INTO auftrag VALUES (?, ?, ?)",
        (201, 999, "Fiktiver Auftrag")
    )
]

for regel, sql, werte in tests:
    try:
        db.execute(sql, werte)
        print(regel, "NICHT erkannt")
    except sqlite3.IntegrityError as fehler:
        print(regel, "erkannt:", fehler)

db.close()

Aufgabe: Führe das Programm lokal aus und erkläre für jede Fehlermeldung, welche betriebliche Regel geschützt wird.

Begründetes Feedback: Eine gute Erklärung nennt nicht nur den SQL-Begriff, sondern verbindet ihn mit dem Geschäftsfall: Pflichtwert schützt Vollständigkeit, `UNIQUE` schützt gegen doppelte Identitäten und `FOREIGN KEY` gegen verwaiste Referenzen.

Gestufte Hilfen

Hilfe 1 – Fachlich denken: Formuliere zuerst die betriebliche Regel ohne SQL, zum Beispiel „Jeder Auftrag braucht einen existierenden Kunden“.
Hilfe 2 – Regeltyp wählen: Fehlt ein Wert, denke an `NOT NULL`. Darf etwas nicht doppelt vorkommen, denke an `UNIQUE`. Verweist eine Tabelle auf eine andere, denke an `FOREIGN KEY`.
Hilfe 3 – SQL ableiten: Markiere die betroffene Spalte und ergänze dort oder als Tabellenregel den passenden Constraint.
Hilfe 4 – Fehler deuten: Prüfe bei einer Fehlermeldung zuerst, welche Regel gerade verletzt wurde, bevor Du den Datensatz veränderst.

Mini-Aufgaben mit Feedback

Basis: Für `inventarnummer` muss immer ein Wert gespeichert werden. Welche Regel passt?

Feedback: `NOT NULL` ist passend, weil die Anforderung die Existenz eines Wertes betrifft.

Anwendung: Eine Gerätenummer darf im gesamten Bestand nur einmal vorkommen. Welche Regel passt?

Feedback: `UNIQUE` ist passend, weil die fachliche Anforderung Eindeutigkeit verlangt.

Transfer: Ein Reparaturauftrag darf nur auf ein vorhandenes Gerät verweisen. Welche Regel passt und warum?

Feedback: Ein `FOREIGN KEY` ist passend, weil eine Beziehung zwischen zwei Tabellen geschützt werden soll. Ohne Referenzschutz könnten Aufträge zu nicht existierenden Geräten entstehen.

Quellen und Medienrechte

PostgreSQL: Offizielle Dokumentation zu Constraints, insbesondere `NOT NULL`, `UNIQUE`, `PRIMARY KEY` und `FOREIGN KEY`: https://www.postgresql.org/docs/current/ddl-constraints.html
SQLite: Offizielle Dokumentation zu Foreign Keys und `PRAGMA foreign_keys`: https://www.sqlite.org/foreignkeys.html
SQLite: Offizielle Dokumentation zu PRAGMA-Anweisungen und Integritätsprüfungen: https://www.sqlite.org/pragma.html
Wikimedia Commons: `Database.svg`, Lizenz CC BY-SA 3.0: https://commons.wikimedia.org/wiki/File:Database.svg
Wikimedia Commons: `Grundbegriffe relationaler Datenbanken.svg`, gemeinfrei: https://commons.wikimedia.org/wiki/File:Grundbegriffe_relationaler_Datenbanken.svg
Wikimedia Commons: `Relational key.svg`, Dateibeschreibung und Lizenzinformationen: https://commons.wikimedia.org/wiki/File:Relational_key.svg
Wikimedia Commons: `Entity Relationship Diagram Examples.png`, Lizenz CC BY-SA 4.0: https://commons.wikimedia.org/wiki/File:Entity_Relationship_Diagram_Examples.png
YouTube: Video zu Foreign Keys von Engineering Digest: https://www.youtube.com/watch?v=0DhykMld5Mk
YouTube: Video zu SQL-Constraints von CodeStudio: https://www.youtube.com/watch?v=2RcjhyxXV6k

Interaktive Aufgaben

Quiz: Teste Dein Wissen

Welche SQL-Regel erzwingt einen Pflichtwert? (NOT NULL) (!UNIQUE) (!FOREIGN KEY) (!ORDER BY)

Welche Regel verhindert doppelte Kundennummern? (UNIQUE) (!NOT NULL) (!SELECT) (!GROUP BY)

Welche Regel schützt eine Beziehung zwischen Auftrag und Kunde? (FOREIGN KEY) (!DEFAULT) (!VIEW) (!ORDER BY)

Was bewirkt ein PRIMARY KEY? (Er identifiziert Datensätze eindeutig) (!Er sortiert jede Abfrage automatisch) (!Er löscht doppelte Tabellen) (!Er verschlüsselt Spalten)

Was bedeutet referentielle Integrität im Beispiel? (Ein Auftrag verweist nur auf einen existierenden Kunden) (!Jeder Kunde hat dieselbe E-Mail-Adresse) (!Jede Tabelle enthält nur eine Spalte) (!Alle Daten werden automatisch gelöscht)

Welche Anweisung aktiviert in SQLite die Fremdschlüsselprüfung für die Verbindung? (PRAGMA foreign_keys = ON) (!SELECT foreign_keys) (!CREATE foreign_keys) (!UPDATE foreign_keys)

Welche Regel schützt die Spalte email vor NULL? (NOT NULL) (!UNIQUE allein) (!FOREIGN KEY) (!INDEX)

Welche Regel passt für eine einmalige Inventarnummer? (UNIQUE) (!NOT NULL allein) (!FOREIGN KEY) (!DELETE)

Was verhindert ON DELETE RESTRICT im Ausbildungsfall? (Das Löschen eines noch referenzierten Kunden) (!Das Anlegen neuer Tabellen) (!Das Lesen von Kundendaten) (!Das Sortieren von Aufträgen)

Warum sind Integritätsregeln direkt in der Datenbank nützlich? (Sie schützen Daten unabhängig von der zugreifenden Anwendung) (!Sie ersetzen jede Datensicherung) (!Sie machen Benutzerrechte überflüssig) (!Sie verschlüsseln automatisch alle Inhalte)

Memory

NOT NULL	Pflichtwert
UNIQUE	Eindeutigkeit
FOREIGN KEY	Referenzschutz
PRIMARY KEY	eindeutige Datensatzkennung
RESTRICT	Löschen bei Abhängigkeit verhindern
NULL	fehlender oder unbekannter Wert

Drag and Drop

Ordne die richtigen Begriffe zu.	Betriebliche Anforderung
NOT NULL	Ein Wert muss vorhanden sein
UNIQUE	Ein Wert darf nicht doppelt vorkommen
PRIMARY KEY	Ein Datensatz braucht eine eindeutige Identität
FOREIGN KEY	Ein Datensatz verweist auf einen anderen Datensatz
RESTRICT	Ein referenzierter Datensatz darf nicht unbedacht gelöscht werden

Kreuzworträtsel

Pflichtwert	Welche Art von Feldanforderung wird mit NOT NULL geschützt?
Eindeutigkeit	Welche Eigenschaft schützt UNIQUE?
Fremdschlüssel	Welcher Schlüssel stellt eine Beziehung zu einer anderen Tabelle her?
Primärschlüssel	Welcher Schlüssel identifiziert einen Datensatz eindeutig?
Integrität	Wie heißt die Eigenschaft konsistenter und regelgerechter Daten?
Restrict	Welche Löschaktion verhindert das Löschen eines noch referenzierten Datensatzes?

LearningApps

Lückentext

Ein Pflichtwert wird in SQL häufig mit

geschützt.

Eine E-Mail-Adresse kann mit

gegen doppelte Werte geschützt werden.

Eine Tabelle erhält mit einem

eine eindeutige Datensatzkennung.

Eine Beziehung zwischen zwei Tabellen wird mit einem

abgesichert.

Die Regel `ON DELETE RESTRICT` schützt einen noch

Datensatz vor dem Löschen.

In SQLite wird die Fremdschlüsselprüfung mit `PRAGMA foreign_keys =

` aktiviert.

Ein fehlender Datenwert wird in SQL häufig mit

dargestellt.

Die Einhaltung betrieblicher Datenregeln unterstützt die

.

Offene Aufgaben

Leicht

Pflichtwert: Nenne drei Felder aus einem fiktiven Ausbildungsbetrieb, die zwingend ausgefüllt sein sollten, und begründe Deine Auswahl.
Eindeutigkeit: Erfinde fünf Inventarnummern und markiere, welche davon bei doppelter Vergabe gegen eine `UNIQUE`-Regel verstoßen würden.
Datenmodell: Zeichne die Tabellen `kunde` und `auftrag` mit ihren wichtigsten Spalten auf Papier oder digital.
Fehleranalyse: Erkläre in eigenen Worten, warum ein Auftrag mit `kunden_id = 999` problematisch ist, wenn es diesen Kunden nicht gibt.

Standard

SQL: Erstelle lokal eine Tabelle `geraet` mit `geraete_id`, `inventarnummer`, `bezeichnung` und passenden Pflicht- sowie Eindeutigkeitsregeln.
Referentielle Integrität: Ergänze lokal eine Tabelle `reparatur`, die über einen Fremdschlüssel auf `geraet` verweist.
Testfall: Entwickle je einen gültigen und einen ungültigen Testfall für `NOT NULL`, `UNIQUE` und `FOREIGN KEY`.
Dokumentation: Erstelle eine Tabelle mit den Spalten „Anforderung“, „Constraint“, „gültiger Test“ und „ungültiger Test“.

Schwer

Datenmodellierung: Entwirf ein kleines Modell für Kunden, Geräte und Reparaturaufträge und begründe jeden Schlüssel.
Löschregel: Vergleiche `RESTRICT` und `CASCADE` für einen fiktiven Betriebsfall und begründe, welche Variante Du einsetzen würdest.
Migration: Beschreibe, wie Du bei einer bestehenden Tabelle vorgehen würdest, wenn alte Daten eine neu geplante `UNIQUE`-Regel verletzen.
Qualitätssicherung: Entwirf einen lokalen automatisierten Test mit mindestens sechs Testfällen, der die wichtigsten Integritätsregeln des Modells überprüft.

Lernkontrolle

Anforderungsanalyse: Ein Betrieb meldet doppelte Gerätenummern und Aufträge ohne gültige Gerätezuordnung. Entwickle passende Datenbankregeln und begründe jede Entscheidung.
Fehlerdiagnose: Ein `INSERT` scheitert mit einer Integritätsverletzung. Beschreibe systematisch, wie Du herausfindest, ob `NOT NULL`, `UNIQUE` oder `FOREIGN KEY` die Ursache ist.
Modellvergleich: Vergleiche ein Datenmodell, das Integritätsregeln nur in der Anwendung prüft, mit einem Modell, das zusätzlich Datenbank-Constraints nutzt.
Transfer: Übertrage das Prinzip des Referenzschutzes auf ein anderes betriebliches Beispiel, etwa Lager, Personal, Maschinen oder Tickets.
Entscheidung: Begründe, wann `ON DELETE RESTRICT` sinnvoller sein kann als automatisches Löschen abhängiger Datensätze.

Lernnachweis

Für einen Lernnachweis solltest Du zeigen, dass Du:

betriebliche Anforderungen in Datenbankregeln übersetzen kannst,
`NOT NULL`, `UNIQUE`, `PRIMARY KEY` und `FOREIGN KEY` fachlich unterscheiden kannst,
ein kleines relationales Datenmodell mit sinnvollen Schlüsseln entwerfen kannst,
lokale SQL-Testfälle mit fiktiven Daten durchführen kannst,
Integritätsfehler verständlich analysieren und begründen kannst,
Risiken von Löschregeln wie `RESTRICT` und `CASCADE` einschätzen kannst,
ausschließlich autorisierte lokale Testumgebungen und keine echten Betriebs- oder Kundendaten verwendest.