MariaDB-Kurs / Spalten und Bedingungen mit ALTER TABLE ändern

ALTER TABLE verändert Spalten, Datentypen und Bedingungen einer vorhandenen Tabelle. Prüfen Sie zuerst, ob bestehende Datensätze die neuen Regeln erfüllen. Trennen Sie das Hinzufügen einer Spalte, das Befüllen vorhandener Zeilen und das Anwenden von Bedingungen, um Fehler und Informationsverluste erkennen zu können.

Aktuelle Definition und Wiederherstellungsweg sichern

Eine Strukturänderung geht über das Austauschen einer Beschriftung hinaus. Kleinere Datentypen sowie neue NOT NULL- oder UNIQUE-Regeln können an vorhandenen Daten scheitern oder Werte verändern. Bewahren Sie SHOW CREATE TABLE, eine geeignete Datensicherung und die Liste abhängiger Abfragen auf. Erproben Sie die Änderung zuerst in einer getrennten Umgebung.

Die Anleitung zu NULL, DEFAULT, NOT NULL und CHECK hilft beim Festlegen neuer Regeln. Bereiten und prüfen Sie vor möglichen Datenverlusten die Wiederherstellung. Der umgekehrte Operationsname stellt entfernte Werte nicht zwangsläufig wieder her.

Änderung Vorher prüfen Risiko
NOT NULL hinzufügen Anzahl und Bedeutung der NULL-Werte Erfordert eine Regel für fehlende Daten
UNIQUE hinzufügen Gruppen gleicher Werte Konflikte können die Änderung verhindern
Texte verkürzen Maximale Zeichenlänge Ablehnung oder Abschneiden
Spalte entfernen Abhängige Abfragen und Aufbewahrung Gespeicherte Werte werden entfernt

Vorhandene Produkte für einen neuen Code vorbereiten

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 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 products_38_lab mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung. Der Name gehört nur zur Übung. Untersuchen Sie vorhandene Definition und Datensätze und bestätigen Sie die Entbehrlichkeit vor einer Entfernung. Erhalten Sie Anwendungstabellen. Das Konto benötigt die passenden Rechte zum Erstellen, Lesen, Bearbeiten und strukturellen Ändern dieser Übungstabelle.

USE shopcourse;
CREATE TABLE products_38_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p38_price CHECK (price >= 0),
  CONSTRAINT ck_p38_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_38_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

Die koreanischen Originaltexte bleiben für die UTF-8-Übung erhalten. Die Kennungen 1 bis 6 stehen für Notizbuch, Kugelschreiber, Becher, Lampe, Ordner und Kabel; die Kategorien bedeuten Schreibwaren, Haushalt und Elektronik. Übernehmen Sie die Zeichenfolgen unverändert und vergleichen Sie anhand der Kennungen.

Die Übung ergänzt bei sechs Zeilen einen Produktcode sku. Er erlaubt zunächst NULL zum Befüllen, bevor er verpflichtend wird. P001 bis P006 sind nur eine Konvention dieses Datensatzes. Legen Sie in einer Anwendung Länge, Stabilität und Geltungsbereich der Eindeutigkeit fest.

Optionale Spalte hinzufügen, befüllen und Anzahlen abgleichen

SHOW CREATE TABLE products_38_lab;
ALTER TABLE products_38_lab ADD COLUMN sku VARCHAR(20) NULL;
UPDATE products_38_lab SET sku=CONCAT('P',LPAD(product_id,3,'0'))
WHERE sku IS NULL;
SELECT COUNT(*) AS rows_total,COUNT(sku) AS known_sku,
 COUNT(DISTINCT sku) AS unique_sku FROM products_38_lab;

Nach ADD COLUMN enthalten die sechs vorhandenen Zeilen NULL in sku. UPDATE befüllt nur fehlende Werte mit P001 bis P006. Danach müssen rows_total, known_sku und unique_sku jeweils 6 sein. Gezählt werden Zeilen, nicht fehlende Codes und unterschiedliche nicht fehlende Codes.

Stoppen Sie bei abweichenden Anzahlen und untersuchen Sie fehlende oder wiederholte Codes vor dem Anwenden der endgültigen Bedingungen. Ein erfolgreiches UPDATE beweist nicht allein, dass die neue Regel anwendbar ist.

Nehmen Sie nicht an, dass ALTER und das folgende UPDATE gemeinsam eine rücknehmbare Transaktion bilden. Strukturänderungen haben eigene Bestätigungsgrenzen. Protokollieren Sie abgeschlossene Stufen, um eine unterbrochene Migration bewusst fortsetzen oder wiederherstellen zu können.

Bedingungen anwenden und Ablehnung separat prüfen

ALTER TABLE products_38_lab
 MODIFY COLUMN sku VARCHAR(20) NOT NULL,
 ADD CONSTRAINT uq_p38_sku UNIQUE(sku),
 ADD CONSTRAINT ck_p38_name CHECK(CHAR_LENGTH(product_name)>0);
SHOW CREATE TABLE products_38_lab;
SELECT product_id,product_name,sku FROM products_38_lab ORDER BY product_id;

Der Code wird verpflichtend und eindeutig. CHECK verlangt außerdem mindestens ein Zeichen im Produktnamen. Dies weist leeren Text ab, entfernt aber keine Leerzeichen und verbietet keinen ausschließlich aus Leerzeichen bestehenden Namen. Definieren Sie bei Bedarf eine andere Regel.

Prüfen und wiederholen Sie bei MODIFY Typ, Länge, NULL-Regel, Vorgaben und alle weiteren zu erhaltenden Attribute. Nehmen Sie nicht an, dass jedes ausgelassene Attribut unverändert bleibt. Die offizielle Referenz zu ALTER TABLE erläutert Spaltendefinitionen und Änderungsoptionen.

Führen Sie den folgenden absichtlichen Fehler separat aus. Produkt 2 soll P001 erhalten, obwohl Produkt 1 den Code bereits verwendet. Erwarten Sie einen Eindeutigkeitsfehler und prüfen Sie anschließend, dass Produkt 2 P002 behält. Halten Sie diese Anweisung außerhalb der Datei, die ohne Fehler weiterlaufen soll.

UPDATE products_38_lab SET sku='P001' WHERE product_id=2;

Prüfen Sie mit SELECT product_id,sku FROM products_38_lab WHERE product_id=2;. Testen Sie auch die Ablehnung eines leeren Namens in einer Übungstransaktion mit aktiven Prüfungen und schließen Sie mit ROLLBACK ab. Unterscheiden Sie erwartete Regelverletzungen von unerwarteten Migrationsfehlern.

Sperren und Umstellungsaufwand untersuchen

Abhängig von Version, Speicher-Engine und Operation kann eine Änderung Datenkopien oder Sperren erfordern. ALGORITHM und LOCK machen nicht jede Änderung unterbrechungsfrei. Prüfen Sie unterstützte Kombinationen und deren Fehlerverhalten in der verwendeten Version, bevor Sie sich darauf verlassen.

Eine lange Transaktion kann eine Metadatensperre halten und ALTER verzögern. Die Dauer bei sechs Zeilen sagt keine Produktionslaufzeit voraus. Berücksichtigen Sie Daten- und Indexumfang, freien Speicher, Wiederherstellungszeit und ein Wartungsfenster oder eine mit der Anwendung verträgliche schrittweise Umstellung.

Spaltenentfernung und Datenwiederherstellung unterscheiden

Das Entfernen der neuen Spalte vernichtet die darin gesammelten Codes. Es stellt nicht zwangsläufig den vorherigen Anwendungszustand her. Informationsverluste verlangen eine vorherige Kopie und geprüfte Wiederherstellung. Bestätigen Sie hier vor der Bereinigung, dass die sechs ursprünglichen Namen, Preise und Bestände unverändert sind.

Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, Definition und Datensätze und sichern Sie benötigte Inhalte. Entfernen Sie die folgende Tabelle nur als weiterhin entbehrliches Übungsobjekt. DROP TABLE entfernt Struktur und Daten und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Halten Sie diese Bereinigung außerhalb der Transaktionsversuche.

DROP TABLE shopcourse.products_38_lab;