MariaDB-Kurs / Zeilen mit PRIMARY KEY, AUTO_INCREMENT und UNIQUE identifizieren
PRIMARY KEY identifiziert jede Zeile eindeutig; UNIQUE begrenzt andere Werte oder Kombinationen, die sich nicht wiederholen dürfen. AUTO_INCREMENT erzeugt ganzzahlige Kennungen, ersetzt aber keine Schlüsseldefinition und garantiert keine lückenlose Folge. Kombinieren Sie die Funktionen entsprechend der Datenidentität und den fachlichen Regeln.
Zeilenidentität und fachliche Eindeutigkeit trennen
Zwei Personen können denselben Namen haben. Ein Name ist deshalb kein guter Zeilenbezeichner. Das Beispiel verwendet eine stabile erzeugte Nummer als Primärschlüssel und behandelt die Eindeutigkeit der E-Mail-Adresse als separate Regel. Die Anleitung zur Tabellenstruktur zeigt die Position dieser Definitionen; Regeln zu vorhandenen und zulässigen Werten ergänzen die Schlüssel.
| Funktion | Aufgabe | Wichtige Bedingung |
|---|---|---|
| PRIMARY KEY | Eine Zeile identifizieren | Eine Definition pro Tabelle; die Spalten erlauben kein NULL und ihre Kombinationen sind eindeutig |
| UNIQUE | Doppelte Werte oder Kombinationen zurückweisen | Mehrere Definitionen möglich; eine für NULL offene eindeutige Spalte kann mehrere NULL-Werte enthalten |
| AUTO_INCREMENT | Bei ausgelassener Kennung eine Ganzzahl erzeugen | Eine Spalte pro Tabelle; sie muss indiziert sein und kann Lücken erzeugen |
Ein Primärschlüssel bedeutet nicht zwangsläufig eine Spalte: Ein zusammengesetzter Schlüssel ist eine Definition mit mehreren Spalten. AUTO_INCREMENT muss nicht in jedem möglichen Entwurf der Primärschlüssel sein. Ein einzelner ganzzahliger Primärschlüssel ist hier jedoch ein einfacher Ausgangspunkt.
Die vollständigen Kunden- und Schlüsselbeispiele 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 customers_key_lab, product_codes_key_lab und auto_gap_lab noch nicht existieren. Untersuchen Sie vorhandene Objekte, bevor Sie CREATE wiederholen. Speichern Sie das gesamte normale Beispiel als UTF-8-Datei und verwenden Sie SOURCE mit dem tatsächlichen lokalen Pfad. Halten Sie absichtliche Fehler getrennt.
customers_key_lab ist eine separate Tabelle zum Lernen von Kennungen vor der Modellierung von Bestellbeziehungen. Die E-Mail-Spalte erlaubt fehlende Informationen. Der Primärschlüssel ist ausdrücklich NOT NULL, wird aber aus den Einfügelisten ausgelassen, damit der Server die Nummer erzeugt:
USE shopcourse;
CREATE TABLE customers_key_lab (
customer_id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(254) NULL,
PRIMARY KEY (customer_id),
CONSTRAINT uq_customers_key_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO customers_key_lab (customer_name, email) VALUES
('Alex Chen', 'alex@example.com'),
('Sam Patel', NULL),
('Jordan Lee', NULL);
SELECT customer_id, customer_name, email FROM customers_key_lab ORDER BY customer_id;
CREATE TABLE product_codes_key_lab (
supplier_id INT NOT NULL,
supplier_code VARCHAR(30) NOT NULL,
label VARCHAR(100) NOT NULL,
PRIMARY KEY (supplier_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO product_codes_key_lab VALUES
(1, 'N001', 'Notebook'),
(2, 'N001', 'Notebook from supplier two');
SELECT supplier_id, supplier_code, label FROM product_codes_key_lab ORDER BY supplier_id;
CREATE TABLE auto_gap_lab (
item_id INT PRIMARY KEY AUTO_INCREMENT,
label VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO auto_gap_lab (label) VALUES ('Rolled-back row');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('Saved row');
SELECT item_id, label FROM auto_gap_lab;
| customer_id | customer_name | |
|---|---|---|
| 1 | Alex Chen | alex@example.com |
| 2 | Sam Patel | NULL |
| 3 | Jordan Lee | NULL |
Bei einer neuen Tabelle und den Standardeinstellungen 1 für Schrittweite und Startversatz entstehen die Nummern 1, 2 und 3. UNIQUE(email) erlaubt die zwei NULL-Adressen und verlangt keine E-Mail-Adresse. Falls sie verpflichtend ist, ergänzen Sie eine geeignete NOT-NULL-Regel und prüfen Sie vorhandene fehlende Werte, bevor Sie diese Regel auf bestehende Daten anwenden.
Die Referenzen zu AUTO_INCREMENT sowie PRIMARY KEY und UNIQUE erläutern die Definitionen. Die example.com-Adressen sind Beispieldaten und keine Ziele für den Versand von Nachrichten.
Doppelte E-Mail und doppelten Primärschlüssel testen
Führen Sie diese Fehler nach der normalen Datei getrennt aus. Die erste Anweisung wiederholt eine E-Mail-Adresse; die zweite gibt die bereits vorhandene Kennung 1 ausdrücklich an:
INSERT INTO customers_key_lab (customer_name, email) VALUES ('Another Alex', 'alex@example.com');
INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, 'Duplicate ID');
Vergleichen Sie die Schlüsselnamen in den Fehlermeldungen. Nach jedem Fehler soll SELECT COUNT(*) FROM customers_key_lab weiterhin drei liefern. Wechseln Sie nicht sofort zu INSERT IGNORE oder REPLACE, um das Problem zu verdecken. REPLACE kann eine vorhandene konfliktbehaftete Zeile ersetzen und ist kein harmloses Ignorieren eines Duplikats.
Die Kollation der E-Mail-Spalte beeinflusst den Duplikatvergleich. Bei utf8mb4_unicode_ci kann geänderte Großschreibung weiterhin einen Konflikt verursachen: ALEX@example.com ist unter dieser Regel nicht zuverlässig von alex@example.com verschieden. Legen Sie Normalisierung und Vergleich bewusst fest, wie in der Anleitung zu Text und Kollation besprochen.
Einen Primärschlüssel aus mehreren Spalten verstehen
Zwei Lieferanten können denselben Code N001 verwenden. Ein Schlüssel allein auf dem Code würde das gültige Produkt des zweiten Lieferanten verhindern. Die Datei definiert deshalb das Paar (supplier_id, supplier_code) als Primärschlüssel:
| supplier_id | supplier_code | Ergebnis |
|---|---|---|
| 1 | N001 | Code des ersten Lieferanten |
| 2 | N001 | Erlaubt: andere Kombination |
| 1 | erneut N001 | Abgewiesen: dieselbe Kombination |
INSERT INTO product_codes_key_lab VALUES (1, 'N001', 'Duplicate code');
Der zusammengesetzte Schlüssel macht die Kombination eindeutig und nicht jeden Bestandteil einzeln. Ein Lieferant kann mehrere Codes besitzen und unterschiedliche Lieferanten können denselben Code verwenden. Abfragen und Fremdschlüsselverweise können das vollständige Paar benötigen. Wenn sich ein fachlicher Schlüssel ändern kann, erwägen Sie eine stabile erzeugte Kennung mit einer separaten UNIQUE-Kombination.
Die englischen Bezeichnungen erhalten die SQL-Beispiele: Notebook bedeutet Notizbuch, Notebook from supplier two bezeichnet ein Notizbuch des zweiten Lieferanten und Duplicate code kennzeichnet den Test eines doppelten Codes.
Mit Lücken in erzeugten Kennungen rechnen
auto_gap_lab fügt eine Zeile innerhalb einer Transaktion ein, rollt die Einfügung zurück und speichert danach eine andere Zeile. In der neuen InnoDB-Tabelle dieses Beispiels hat die gespeicherte Zeile die Kennung 2 und die Bezeichnung Saved row, also gespeicherte Zeile. Rolled-back row, die zurückgerollte Zeile, bleibt nicht bestehen. Die dafür reservierte Kennung kehrt jedoch nicht in die Folge zurück.
Auch Löschungen, fehlgeschlagene Einfügungen und gleichzeitige Arbeiten können Lücken hinterlassen. AUTO_INCREMENT ist daher weder eine Zeilenanzahl noch eine Garantie für lückenlose Belegnummern. Zählen Sie Zeilen mit COUNT(*); MAX(id) liefert nur die höchste sichtbare Kennung.
Fragen Sie den erzeugten Wert mit LAST_INSERT_ID() in derselben Verbindung unmittelbar nach der betreffenden erfolgreichen Einfügung ab. Vermischen Sie dies nicht mit der Arbeit einer anderen Verbindung und interpretieren Sie einen Wert nach einem Fehler nicht als Erfolgsnachweis. Bei einer Einfügung mehrerer Zeilen liefert die Funktion die erste erzeugte Kennung. Wer MAX(id)+1 selbst berechnet, ermöglicht gleichzeitigen Clients die Auswahl derselben nächsten Nummer.
Schlüssel prüfen und die isolierten Tabellen zurücksetzen
SHOW CREATE TABLE customers_key_lab;
SHOW INDEX FROM customers_key_lab;
SHOW CREATE TABLE product_codes_key_lab;
Prüfen Sie, dass PRIMARY und uq_customers_key_email getrennte Schlüssel sind und der zusammengesetzte Primärschlüssel beide vorgesehenen Spalten in der gewünschten Reihenfolge umfasst. Das Feld Extra von DESCRIBE und SHOW INDEX beantworten verschiedene Fragen: Werterzeugung und Eindeutigkeit sind unterschiedliche Funktionen.
Prüfen Sie vor einer Wiederholung SELECT DATABASE() und SHOW CREATE TABLE mit jedem tatsächlichen Namen, um die drei entbehrlichen Tabellen dieser Übung zu bestätigen. Sichern Sie benötigte Daten und entfernen Sie erst danach diese Ziele:
DROP TABLE shopcourse.customers_key_lab, shopcourse.product_codes_key_lab, shopcourse.auto_gap_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 die normale Datei erneut ausführen. Die Tabelle products aus der ersten Produktübung bleibt von diesem Zurücksetzen ausgenommen.




