MariaDB-Kurs / Eins-zu-viele, Viele-zu-viele und Fremdschlüssel entwerfen
Entwerfen Sie Tabellenbeziehungen, indem Sie die Bedeutung jeder Zeile festlegen und Primär- mit Fremdschlüsseln verbinden. Ein Kunde kann mehrere Bestellungen haben. Die Viele-zu-viele-Beziehung zwischen Bestellungen und Produkten wird durch Bestellpositionen mit Mengen und den beim Kauf gespeicherten Einzelpreisen abgebildet.
Zeilen und Richtung jeder Beziehung festlegen
Speichern Sie Kundennamen und Produktlisten als einen einzigen Bestellungstext, müssen Sie diesen Text für Mengenberechnungen aufteilen und wiederholte Angaben bei Änderungen pflegen. Trennen Sie Kunden, Produkte, Bestellungen und Positionen nach den benötigten Fakten und Aufbewahrungsregeln. Eine Aufteilung braucht einen fachlichen Zweck.
| Übungstabelle | Eine Zeile steht für | Identifizierung oder Beziehung |
|---|---|---|
| c25_customers | Einen Kunden | Primärschlüssel customer_id |
| c25_products | Eine Produktart | Primärschlüssel product_id |
| c25_orders | Eine Bestellung | customer_id verweist auf den Kunden |
| c25_items | Ein Produkt in einer Bestellung | Zusammengesetzter Primärschlüssel order_id + product_id |
Jede Position speichert in unit_price den Einzelpreis beim Kauf. So lässt sich der Betrag einer älteren Bestellung auch nach einer Änderung des aktuellen Produktpreises nachvollziehen. Darf dasselbe Produkt mit unterschiedlichen Optionen mehrfach in einer Bestellung erscheinen, prüfen Sie eine Positionskennung oder einen Optionsschlüssel. Der vereinfachte zusammengesetzte Schlüssel hier erlaubt nur eine Zeile pro Kombination aus Bestellung und Produkt.
Das Schema erlaubt Kunden ohne Bestellungen und Produkte ohne Verkäufe. Jede Bestellung benötigt einen Kunden, jede Position eine Bestellung und ein Produkt. Fremdschlüssel allein erzwingen keine Mindestanzahl von Positionen pro Bestellung; dafür ist eine zusätzliche Regel nötig. Die offizielle Referenz zu Fremdschlüsseln erklärt die Integritätsprüfungen.
Das vollständige Beispiel mit Fremdschlüsseln erstellen
Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Die Anleitung zu Client und UTF-8-SQL-Dateien erklärt das Speichern und Ausführen mit SOURCE C:/mariadb-lab/lesson.sql;. Ersetzen Sie den Pfad durch die tatsächliche Datei und führen Sie SQL im MariaDB-Client aus.
Prüfen Sie vor dem Erstellen von c25_customers, c25_products, c25_orders und c25_items mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung. Untersuchen Sie vorhandene Definitionen und Zeilen, bevor Sie über einen Neustart entscheiden. Verwenden Sie nur entbehrliche Übungstabellen und erhalten Sie Anwendungstabellen. Prüfen Sie, dass @@SESSION.foreign_key_checks und @@SESSION.check_constraint_checks aktiv sind.
Erstellen und befüllen Sie zuerst Kunden und Produkte, danach die darauf verweisenden Bestellungen und Positionen. Die Anleitung zu Primärschlüsseln und zusammengesetzten Schlüsseln erklärt die Identifizierung.
USE shopcourse;
CREATE TABLE c25_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Das SQL erhält die koreanischen Beispieldaten für Namen und Städte; die Erläuterungen verwenden eindeutige Kennungen. Die Produkte 1 bis 4 sind Notizbuch, Kugelschreiber, Becher und Lampe. Die Kunden 1 und 3 haben die Stadt Seoul, Kunde 2 Busan und Kunde 4 Daejeon. Behalten Sie die UTF-8-Zeichenfolgen beim Nachvollziehen der Ergebnisse bei. Es handelt sich um Übungsdaten.
Eins-zu-viele- und Viele-zu-viele-Beziehungen kontrollieren
SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;
Es gibt vier Kunden, vier Bestellungen und sechs Positionen. Kunde 1 hat die Bestellungen 101 und 102. Bestellung 101 enthält die Produkte 1 und 2; Produkt 1 erscheint in den Bestellungen 101 und 103. Jede Verbindung von Bestellung und Produkt ist eine Zeile mit Menge und historischem Einzelpreis.
Die JOIN-Abfrage verbindet Zeilen über customer_id, um diese Beziehungen sichtbar zu machen. Sie zeigt nur Kunden mit einer passenden Bestellung. Dass Kunde 4 in dieser Ausgabe fehlt, bedeutet keine Löschung aus customers. Beziehungen verwenden Kennungen: Namen können mehrfach vorkommen und sich ändern.
Fehlende Referenzen und eingeschränktes Löschen testen
Führen Sie nach der gültigen SQL-Datei die beiden folgenden Fehlerprüfungen einzeln aus. Die erste verweist auf den nicht vorhandenen Kunden 999. Die zweite versucht, Kunde 1 zu löschen, obwohl Bestellungen auf ihn verweisen.
INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');
DELETE FROM c25_customers WHERE customer_id=1;
Beide Anweisungen müssen an der Fremdschlüsselprüfung scheitern. Prüfen Sie nach jedem Fehler, dass Kunde 1 weiter existiert und die Anzahl der Bestellungen vier bleibt. Halten Sie diese Versuche außerhalb der normalen Datei, weil ein Fehler nachfolgende Anweisungen stoppen kann. Lassen Sie foreign_key_checks bei der Untersuchung der Eingabe aktiv.
In einem anderen Entwurf kann ein Fremdschlüssel mit zulässigem NULL einen noch nicht zugeordneten übergeordneten Datensatz darstellen. Hier verhindert customer_id mit NOT NULL in Bestellungen diesen Zustand. Die Anleitung zu Eingabebedingungen erläutert Anwesenheit und weitere Regeln.
Eine Löschregel bewusst auswählen
CASCADE kann das Löschen eines übergeordneten Datensatzes auf abhängige Zeilen übertragen. Bequemlichkeit reicht als Auswahlgrund nicht aus: Je nach Anwendungszweck müssen Teile eines Datensatzes erhalten, anonymisiert oder gelöscht werden. Bestellpositionen können auch für Rückgaben und Verlaufsauswertungen gebraucht werden. RESTRICT verhindert hier, dass verbundene Bestellungen automatisch gelöscht werden.
Prüfen Sie kompatible Datentypen und Vorzeichen, Indizes und die Speicher-Engine. SHOW CREATE TABLE und eine absichtlich ungültige Eingabe prüfen mehr als das bloße Wort REFERENCES in einer Datei. Ein Fremdschlüssel kontrolliert Referenzen, aber nicht jede Geschäftsregel, etwa ausreichend Bestand zur Erfüllung einer Bestellung.
Optionale Beziehungen prüfen und abhängige Tabellen zuerst entfernen
Prüfen Sie, welche Datensätze vor einer Beziehung existieren dürfen: Kunden ohne Bestellungen, Produkte ohne Verkäufe oder Bestellungen, die einen Kunden benötigen. Unterscheiden Sie aktuelle Eigenschaften von Kaufdaten. Eine Änderung des Katalogpreises muss unit_price einer historischen Position erhalten, wenn der ursprüngliche Betrag reproduzierbar bleiben soll.
Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, SHOW CREATE TABLE für alle vier Tabellen und ihre gespeicherten Zeilen. Sichern Sie benötigte Inhalte und entfernen Sie nur die entbehrlichen Übungstabellen, zuerst die abhängigen und danach die referenzierten Tabellen. DROP TABLE entfernt Definition und Daten und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Trennen Sie die Bereinigung von Transaktionsversuchen.
DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;





