SQLite CREATE TABLE: синтаксис и создание связанных таблиц

Когда я впервые начал работать с SQLite, синтаксис CREATE TABLE казался простым — пока не столкнулся с составными ключами и внешними ключами в одной таблице. В этом руководстве разберём оператор от базового синтаксиса до реального примера с тремя связанными таблицами

Чтобы создать новую таблицу в SQLite, используйте оператор CREATE TABLE со следующим синтаксисом:

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name (
    column_1 data_type PRIMARY KEY,
    column_2 data_type NOT NULL,
    column_3 data_type DEFAULT 0,
    table_constraints
) [WITHOUT ROWID];

Разбор синтаксиса оператора CREATE TABLE

Разберём роль каждого элемента синтаксиса перед переходом к практическим примерам

Имя таблицы. Укажите имя таблицы после команды CREATE TABLE. Имя не должно начинаться с sqlite_, поскольку этот префикс зарезервирован для внутреннего использования SQLite

IF NOT EXISTS. Этот параметр позволяет создать таблицу лишь в том случае, если она ещё не существует. Попытка создания уже существующей таблицы без этого параметра приведёт к ошибке

schema_name. Если необходимо, укажите схему, к которой будет относиться новая таблица. Схема может быть основной базой данных, временной базой данных или любой присоединённой базой

Список столбцов. Каждый столбец имеет своё имя, тип данных и ограничения. SQLite поддерживает такие ограничения, как PRIMARY KEY, UNIQUE, NOT NULL и CHECK

Ограничения таблицы. Помимо ограничений столбцов, можно задать ограничения на уровне всей таблицы: PRIMARY KEY, FOREIGN KEY, UNIQUE и CHECK

По умолчанию каждая запись в таблице имеет скрытый столбец, называемый rowid, который хранит уникальный 64-битный целочисленный ключ для идентификации строки. Если вы хотите избежать его создания, используйте параметр WITHOUT ROWID, который доступен с версии SQLite 3.8.2

Первичный ключ (primary key) таблицы — это столбец или группа столбцов, которые однозначно идентифицируют каждую строку

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Практический пример: три связанные таблицы SQLite

Предположим, вам нужно управлять контактами с помощью SQLite. Каждый контакт содержит следующую информацию:

• Имя
• Фамилия
• Электронная почта
• Телефон

Требование состоит в том, что электронная почта и телефон должны быть уникальными. Кроме того, каждый контакт принадлежит одной или нескольким группам, а каждая группа может содержать ноль или несколько контактов

На основе этих требований мы разработали три таблицы:

• Таблица contacts — хранит информацию о контактах
• Таблица groups — хранит информацию о группах
• Таблица contact_groups — хранит связь между контактами и группами

Следующая диаграмма базы данных иллюстрирует таблицы contacts, groups и contact_groups

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

Следующий оператор создаёт таблицу contacts:

CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name  TEXT NOT NULL,
    email      TEXT NOT NULL UNIQUE,
    phone      TEXT NOT NULL UNIQUE
);

Столбец contact_id служит первичным ключом таблицы contacts. Поскольку первичный ключ состоит из одного столбца, можно использовать ограничение столбца

Столбцы first_name и last_name имеют тип данных TEXT и ограничение NOT NULL — это означает обязательное указание значений при вставке или обновлении строк в таблице contacts

Поля email и phone должны быть уникальными, поэтому для каждого из них используется ограничение UNIQUE

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

Следующий оператор создаёт таблицу groups:

CREATE TABLE groups (
    group_id INTEGER PRIMARY KEY,
    name     TEXT NOT NULL
);

Таблица groups довольно проста и содержит два столбца: group_id и name. Столбец group_id является столбцом первичного ключа

Таблица contact_groups: составной первичный ключ и внешние ключи

Следующий оператор создаёт таблицу contact_groups:

CREATE TABLE contact_groups (
    contact_id INTEGER,
    group_id   INTEGER,
    PRIMARY KEY (contact_id, group_id),
    FOREIGN KEY (contact_id)
        REFERENCES contacts (contact_id)
        ON DELETE CASCADE ON UPDATE NO ACTION,
    FOREIGN KEY (group_id)
        REFERENCES groups (group_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

Таблица contact_groups имеет первичный ключ, состоящий из двух столбцов: contact_id и group_id. Чтобы добавить составное ограничение первичного ключа на уровне таблицы, используется следующий синтаксис:

PRIMARY KEY (contact_id, group_id)

Кроме того, столбцы contact_id и group_id являются внешними ключами (foreign keys). Для определения внешнего ключа каждого столбца используется ограничение FOREIGN KEY:

FOREIGN KEY (contact_id)
    REFERENCES contacts (contact_id)
    ON DELETE CASCADE ON UPDATE NO ACTION

FOREIGN KEY (group_id)
    REFERENCES groups (group_id)
    ON DELETE CASCADE ON UPDATE NO ACTION

На практике я всегда явно прописываю поведение ON DELETE и ON UPDATE, чтобы избежать неожиданных последствий при удалении связанных записей. Мой совет — делать это даже тогда, когда поведение по умолчанию кажется очевидным: явное лучше неявного. Ограничение FOREIGN KEY будет подробно рассмотрено в отдельном руководстве

Частые ошибки при создании таблиц в SQLite

На нашем опыте работы с SQLite чаще всего встречаются несколько одних и тех же проблем при использовании CREATE TABLE

Имя таблицы начинается с sqlite_. SQLite резервирует этот префикс для системных таблиц. Попытка создать таблицу с таким именем завершится ошибкой

Отсутствие IF NOT EXISTS при повторном запуске скрипта. Если скрипт создания таблицы запускается повторно без этого параметра, SQLite вернёт ошибку о том, что таблица уже существует. Добавьте IF NOT EXISTS, чтобы сделать скрипт идемпотентным

Составной первичный ключ, заданный как ограничение столбца. Если первичный ключ охватывает несколько столбцов, его нельзя объявить на уровне отдельного столбца — только через ограничение таблицы PRIMARY KEY (col1, col2)

Использование WITHOUT ROWID в старых версиях SQLite. Параметр доступен только начиная с версии 3.8.2. В более ранних версиях оператор завершится ошибкой синтаксиса

Отсутствие NOT NULL там, где оно нужно. SQLite по умолчанию разрешает NULL в любом столбце, кроме первичного ключа. Если поле обязательно, явно укажите NOT NULL

Оператор CREATE TABLE в SQLite позволяет гибко описывать структуру данных: задавать типы столбцов, ограничения на уровне столбцов и таблицы, составные первичные ключи и внешние ключи. Параметр IF NOT EXISTS делает скрипты безопасными для повторного запуска, а WITHOUT ROWID даёт контроль над внутренним устройством таблицы там, где это действительно нужно

Ответы на эти вопросы могут быть для вас полезными

Можно ли создать таблицу без первичного ключа в SQLite? Да. SQLite не требует обязательного объявления первичного ключа. Если первичный ключ не задан, SQLite всё равно создаёт внутренний столбец rowid, если не указан параметр WITHOUT ROWID

Чем отличается ограничение столбца от ограничения таблицы? Ограничение столбца объявляется прямо после определения конкретного столбца и применяется только к нему. Ограничение таблицы объявляется отдельной строкой после всех столбцов и может охватывать несколько столбцов — например, составной первичный ключ

Что произойдёт, если попытаться создать таблицу с именем, начинающимся на sqlite_? SQLite вернёт ошибку, поскольку этот префикс зарезервирован для внутренних системных таблиц

Когда стоит использовать WITHOUT ROWID? Этот параметр полезен для таблиц с небольшими строками и часто используемым первичным ключом — он может улучшить производительность за счёт устранения дублирования данных между первичным ключом и внутренним rowid. Доступен только в SQLite 3.8.2 и выше

Как задать уникальность сразу для нескольких столбцов? Используйте ограничение таблицы UNIQUE (col1, col2). Это гарантирует уникальность комбинации значений, а не каждого столбца по отдельности