Tabellen erstellen und löschen

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255),
    age INTEGER
);

INTEGER und VARCHAR(255) sind Datentypen: ein numerischer und ein String-Typ. Mehr dazu in den folgenden Artikeln.

• SERIAL oder GENERATED ALWAYS AS IDENTITY

  Gibt an, dass der Wert dieser Spalte beim Einfügen neuer Datensätze automatisch hochgezählt wird. SERIAL ist die Kurzform, um ein Autoinkrement-Feld anzulegen.

MySQL 8.4
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18
);

• id ein Feld vom Typ SERIAL (autoinkrementierende Ganzzahl), das den Primary Key bildet;

MySQL 8.4
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Um die Struktur einer angelegten Tabelle anzuschauen, kannst du eine SQL-Query gegen das Informationsschema absetzen:

MySQL 8.4
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = current_schema() AND table_name = 'users';

Primary Key

Wenn du den Primary Key nicht direkt bei den Spalteneigenschaften definierst, kannst du das auch in den Tabellen-Parametern tun, indem du nach der Spaltenliste PRIMARY KEY (spalte [, ...]) ergänzt:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    PRIMARY KEY (id)
);

Foreign Keys

Angenommen, wir wollen speichern, in welcher Firma unsere Nutzer arbeiten. Legen wir eine kleine Tabelle Companies an, in der wir die eindeutige ID und den Namen der Firma speichern:

MySQL 8.4
CREATE TABLE Companies (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

Als Nächstes ergänzen wir in der Users-Tabelle das Feld company – den Arbeitsplatz unseres Nutzers, der auf einen Datensatz in Companies verweist. Die vollständige Query zum Anlegen der Tabelle sieht so aus:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id)
);

Damit beim Einfügen neuer Datensätze in Users garantiert ist, dass die Spalte company eine ID enthält, die es in Companies wirklich gibt, wird ein Foreign Key verwendet. Seine Syntax:

FOREIGN KEY (spalte [, ...])
REFERENCES externe_tabelle (spalte_der_externen_tabelle [, ...])
[ON DELETE aktion]
[ON UPDATE aktion]

Die vollständige Query mit Foreign Key sieht so aus:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (company) REFERENCES Companies (id)
);

Mit Foreign Keys kann man festlegen, wie sich der aktuelle Datensatz verhält, wenn der referenzierte Datensatz geändert oder gelöscht wird.

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (company) REFERENCES Companies (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

ON DELETE RESTRICT bedeutet: Versucht jemand, eine Firma zu löschen, zu der es Einträge in Users gibt, lässt die Datenbank das nicht zu:

MySQL 8.4
ERROR:  update or delete on table "companies" violates foreign key constraint "users_company_fkey" on table "users"
DETAIL:  Key (id)=(1) is still referenced from table "users".

Wäre ON DELETE CASCADE angegeben, würden beim Löschen einer Firma auch alle Nutzer gelöscht, die auf diese Firma verweisen.

Es gibt noch eine Option: ON DELETE SET NULL. Damit setzt die Datenbank für alle Nutzer der gelöschten Firma das Feld company auf NULL.

ON UPDATE CASCADE bedeutet: Ändert eine Firma ihre ID, bekommen alle Nutzer (Users) automatisch die neue ID im Feld company.