Создание и удаление таблиц

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

INTEGER, VARCHAR(255) - типы данных: числовой и строковый соответственно. Более подробно о них можно будет узнать в следующих статьях.

• SERIAL или GENERATED ALWAYS AS IDENTITY

  Указывает, что значение данной колонки будет автоматически увеличиваться при добавлении новых записей в таблицу. SERIAL — это сокращение для создания автоинкрементного поля.

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

• id - поле типа SERIAL (автоинкрементное целое число), являющееся первичным ключом;

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

Для того чтобы посмотреть описание созданной таблицы, можно воспользоваться SQL запросом к информационной схеме:

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 (столбец [, ...]) после перечисления столбцов:

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

Внешние ключи

Предположим, что мы хотим хранить данные о компании, в которой работают наши пользователи. Давайте создадим небольшую таблицу Companies, в которой мы будем хранить уникальный идентификатор и название компании:

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

Дальше нужно добавить в таблицу Users поле company – место работы нашего пользователя, которое будет ссылаться на запись в таблице Companies. Полный запрос для создания таблицы будет выглядеть так:

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

Для того, чтобы при добавлении новых записей в таблицу Users гарантировать, что в колонке company находится идентификатор, существующий в таблице Companies, используется внешний ключ. Он имеет следующий синтаксис:

FOREIGN KEY (столбец [, ...])
REFERENCES внешняя_таблица (столбец_внешней_таблицы [, ...])
[ON DELETE действие]
[ON UPDATE действие]

Полный запрос для создания таблицы с внешним ключом будет таким:

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)
);

При наличии внешних ключей можно определить поведение текущей записи, при изменении или удалении записи, на которую она ссылается.

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 означает, что если попробовать удалить компанию, у которой в таблице Users есть данные, база данных не даст этого сделать:

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".

Если бы было указано ON DELETE CASCADE, то при удалении компании были бы удалены все пользователи, ссылающиеся на эту компанию.

Есть ещё одна опция — ON DELETE SET NULL. При её использовании база данных запишет NULL в качестве значения поля company для всех пользователей, работавших в удалённой компании.

ON UPDATE CASCADE означает, что если компания изменит свой идентификатор, то все пользователи (Users) получат новый идентификатор в поле company.