Структура таблицы базы данных на примере SQLite

Структура таблицы базы данных описывает, какие в ней есть столбцы, какого они типа и какие правила действуют для значений. Ниже на примерах SQLite разобраны поля и записи, выбор имен, типы данных, ограничения PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT и FOREIGN KEY, а также просмотр и изменение схемы готовой таблицы.

Из чего состоит таблица

Реляционная таблица похожа на лист электронной таблицы со строгими правилами. Набор столбцов и их свойств задается один раз командой CREATE TABLE и называется схемой. Данные затем добавляются строками, и каждая строка обязана соответствовать схеме.

Порядок строк в таблице не гарантирован: если нужна сортировка, ее задают в запросе через ORDER BY. Порядок столбцов определяется схемой, но в запросах лучше перечислять поля явно, а не полагаться на SELECT *.

Как называть таблицы и столбцы

Имя столбца должно объяснять содержимое без комментариев. По ошибкам в именах легко понять, что схему проектировали наспех, а переименовывать поля в рабочей базе дорого: придется править все запросы.

• латиница в нижнем регистре и подчеркивания: created_at, user_id;
• без пробелов, дефисов и зарезервированных слов SQL вроде order, group, select;
• единый стиль для таблиц: либо везде множественное число (users, orders), либо везде единственное;
• внешний ключ называется по таблице, на которую ссылается: product_id ссылается на products;
• булевы поля читаются как вопрос: is_active, has_discount.

Если имя все же совпадает с ключевым словом, его берут в двойные кавычки: "order". Работает, но каждый запрос с таким полем становится неудобным.

Типы данных в SQLite

SQLite хранит значения в пяти классах: NULL, INTEGER, REAL, TEXT и BLOB. Тип, указанный в схеме, задает лишь предпочтение (affinity), поэтому в обычной таблице в поле INTEGER можно сохранить строку, и ошибки не будет.

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES ('abc');
SELECT typeof(x) FROM t;   -- text

Строгую проверку включает слово STRICT после определения таблицы (SQLite 3.37 и новее). Тогда та же вставка завершится ошибкой cannot store TEXT value in INTEGER column.

Подробнее о классах хранения и правилах приведения рассказано в статье про типы данных SQLite.

Ограничения столбцов и таблицы

Ограничения не дают записать в таблицу заведомо неверные данные. Проверку выполняет сама СУБД, поэтому правило работает независимо от того, из какой программы пришел запрос.

• PRIMARY KEY - уникальный идентификатор записи. Столбец INTEGER PRIMARY KEY в SQLite заполняется автоматически.
• NOT NULL - поле обязательно для заполнения.
• UNIQUE - значение не повторяется в других строках, например email.
• DEFAULT - значение, если поле не указано в INSERT.
• CHECK - произвольное условие, например цена не меньше нуля.
• FOREIGN KEY - ссылка на запись в другой таблице.

Ограничение пишут либо сразу после типа столбца, либо отдельной строкой в конце определения таблицы. Второй вариант нужен для составных правил, например UNIQUE (user_id, product_id) или первичного ключа из двух полей.

Пример схемы из двух таблиц

Таблицы товаров и заказов связаны внешним ключом: каждый заказ ссылается на существующий товар. Связь подробно разобрана в материале про первичный и внешний ключ.

CREATE TABLE products (
    id       INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    price    REAL CHECK (price >= 0),
    created  TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL REFERENCES products(id),
    quantity    INTEGER NOT NULL DEFAULT 1
);

В SQLite проверка внешних ключей по умолчанию выключена. Ее нужно включать в каждом новом подключении командой PRAGMA foreign_keys = ON, иначе заказ на несуществующий товар будет сохранен без ошибки. После включения такая вставка вызывает FOREIGN KEY constraint failed, а отрицательная цена - CHECK constraint failed.

Как посмотреть структуру готовой таблицы

В консоли sqlite3 схему выводит команда .schema products. Из программы удобнее использовать PRAGMA table_info, она возвращает по строке на каждый столбец: номер, имя, тип, флаг NOT NULL, значение по умолчанию и признак первичного ключа.

import sqlite3

conn = sqlite3.connect('shop.db')
for cid, name, col_type, notnull, default, pk in conn.execute(
        'PRAGMA table_info(products)'):
    print(name, col_type, 'NOT NULL' if notnull else '', 'PK' if pk else '')

sql = conn.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'products'").fetchone()[0]
print(sql)  # исходный текст CREATE TABLE
conn.close()

Таблица sqlite_master хранит текст всех команд создания таблиц и индексов, по ней же можно получить список таблиц базы. Работа с модулем sqlite3 подробно описана в статье Python sqlite. Если удобнее графический интерфейс, структуру показывает вкладка Database Structure в DB Browser for SQLite.

Изменение структуры таблицы

Команда ALTER TABLE в SQLite умеет немного: переименовать таблицу, добавить столбец, переименовать столбец (с версии 3.25) и удалить столбец (с версии 3.35).

ALTER TABLE products ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1;
ALTER TABLE products RENAME COLUMN name TO title;
ALTER TABLE products DROP COLUMN is_active;

При добавлении столбца с NOT NULL обязательно указывать DEFAULT, иначе существующим строкам нечем заполнить новое поле. Изменить тип или ограничение уже созданного столбца нельзя. Для этого создают новую таблицу с нужной схемой, копируют данные запросом INSERT INTO new SELECT ... FROM old, удаляют старую и переименовывают новую.

Частые вопросы

Ответы на вопросы о проектировании таблиц, которые задают чаще всего.

Обязателен ли первичный ключ?

Формально нет, SQLite создаст скрытый столбец rowid. На практике явный id INTEGER PRIMARY KEY нужен почти всегда: по нему удобно обновлять и удалять конкретную запись и ссылаться на нее из других таблиц.

Чем NULL отличается от пустой строки?

NULL означает отсутствие значения, а пустая строка - известное значение нулевой длины. Сравнение x = NULL всегда дает неизвестность, поэтому для проверки пишут x IS NULL.

Где хранить дату в SQLite?

В поле TEXT в формате ISO 8601. Такие строки правильно сортируются и понимаются встроенными функциями date(), datetime() и strftime(). Альтернатива - целое число секунд Unix в поле INTEGER.

Сколько столбцов можно сделать в таблице?

По умолчанию SQLite допускает до 2000 столбцов. Если полей становится больше пары десятков, обычно это сигнал разбить данные на несколько связанных таблиц.