Урок 8. Ключи, связи и индексы

Ключи следят, чтобы данные не противоречили друг другу: не было двух товаров с одним номером и заказов от несуществующих покупателей. Индексы ускоряют поиск. Урок продолжает учебную базу: products, customers и orders из прошлых уроков.

Первичный ключ

Первичный ключ (PRIMARY KEY) — столбец, значение которого однозначно указывает на строку. Он уникален и не бывает пустым. У каждой таблицы курса это id: по номеру 3 всегда находится ровно один товар — рюкзак.

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

Ключ бывает и составным — из нескольких столбцов. Тогда уникальна комбинация значений:

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    qty        INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Здесь в одном заказе товар может встретиться только один раз, но в разных заказах — сколько угодно.

Внешний ключ

Внешний ключ (FOREIGN KEY) — столбец, который ссылается на первичный ключ другой таблицы. В заказах это customer_id и product_id. Пока таблица orders создана без внешних ключей, в неё можно записать заказ от покупателя номер 99, которого нет. Пересоздайте её с проверкой:

PRAGMA foreign_keys = ON;

DROP TABLE orders;

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    product_id  INTEGER NOT NULL REFERENCES products (id),
    qty         INTEGER NOT NULL CHECK (qty > 0),
    order_date  TEXT    NOT NULL
);

INSERT INTO orders (customer_id, product_id, qty, order_date) VALUES
    (1, 1, 10, '2026-09-01'),
    (1, 3, 1, '2026-09-01'),
    (2, 6, 1, '2026-09-03'),
    (3, 2, 20, '2026-09-05'),
    (3, 1, 5, '2026-09-10'),
    (4, 7, 1, '2026-09-12'),
    (2, 4, 2, '2026-09-15');

REFERENCES customers (id) значит: в customer_id может стоять только номер, который есть в customers.id. Та же запись в конце списка столбцов: FOREIGN KEY (customer_id) REFERENCES customers (id).

Теперь база не даст нарушить связи:

INSERT INTO orders (customer_id, product_id, qty, order_date)
VALUES (9, 1, 1, '2026-09-20');

FOREIGN KEY constraint failed

Покупателя номер 9 нет — заказ не записан. Удалить покупателя, у которого есть заказы, тоже нельзя: DELETE FROM customers WHERE id = 1; закончится той же ошибкой. PostgreSQL пишет подробнее: violates foreign key constraint "orders_customer_id_fkey" — с именем нарушенного ключа.

Что делать при удалении

Что происходит со ссылками при удалении строки, на которую ссылаются, задаёт ON DELETE:

CREATE TABLE reviews (
    id          INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL,
    body        TEXT    NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
);

Виды связей

• Один ко многим — самая частая: у покупателя много заказов, у заказа один покупатель. Внешний ключ ставится на стороне «многих» (orders.customer_id).
• Многие ко многим — у заказа много товаров, и товар есть во многих заказах. Такую связь хранят в отдельной таблице-связке с двумя внешними ключами — как order_items выше. В учебной orders упрощение: один товар на заказ.
• Один к одному — строке одной таблицы соответствует не больше одной строки другой (например, паспортные данные сотрудника отдельно от общих). Внешний ключ с UNIQUE.

UNIQUE — уникальность без ключа

UNIQUE запрещает повтор значения в столбце, который не является первичным ключом: адрес почты, номер телефона, артикул. email TEXT UNIQUE — и вторая строка с тем же адресом не запишется: UNIQUE constraint failed.

Пустые значения (NULL) уникальность не нарушают: в SQLite, MySQL и PostgreSQL строк с NULL в таком столбце может быть сколько угодно.

Индексы

Без подсказок СУБД ищет строки перебором — читает таблицу целиком. На восьми товарах это незаметно, на миллионе строк — секунды на каждый запрос. Индекс — отдельная упорядоченная структура, как алфавитный указатель в конце книги: по значению столбца она сразу говорит, где лежат нужные строки.

CREATE INDEX idx_orders_customer ON orders (customer_id);

idx_orders_customer — имя индекса (любое, но лучше понятное), в скобках — столбцы. Запросы менять не нужно: СУБД сама решает, когда индекс полезен. Увидеть её решение помогает EXPLAIN QUERY PLAN в SQLite:

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;

До создания индекса в ответе SCAN orders — перебор всей таблицы. После:

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

SEARCH … USING INDEX — поиск по индексу. В MySQL и PostgreSQL то же показывает EXPLAIN SELECT ….

Какие индексы создаются сами

Для PRIMARY KEY и UNIQUE индекс создаётся автоматически. Для внешних ключей — по-разному: MySQL создаёт его сам, а SQLite и PostgreSQL — нет. Столбцы внешних ключей участвуют почти в каждом JOIN, поэтому индекс на них обычно стоит создать вручную.

Когда индекс нужен, а когда мешает

Индекс полезен на столбцах, которые часто стоят в WHERE, JOIN … ON и ORDER BY больших таблиц. Но у него есть цена: он занимает место, а каждый INSERT, UPDATE и DELETE должен обновить ещё и индекс. Индекс на каждый столбец «на всякий случай» замедлит запись и почти не ускорит чтение.

• Индекс на нескольких столбцах (order_date, customer_id) помогает искать по order_date и по паре, но обычно не помогает при поиске только по customer_id — второму столбцу.
• Поиск LIKE '%ноут%' (по середине текста) индекс не ускоряет: начало значения неизвестно.
• На маленькой таблице индекс бесполезен — перебор и так быстрый.

Удалить индекс: DROP INDEX idx_orders_customer; в SQLite и PostgreSQL, DROP INDEX idx_orders_customer ON orders; в MySQL. Данные при этом не пропадают. Уникальный индекс — CREATE UNIQUE INDEX: он и ускоряет поиск, и запрещает повторы.

Коротко

• PRIMARY KEY — уникальный обязательный идентификатор строки, бывает составным.
• REFERENCES (внешний ключ) не даёт сослаться на несуществующую строку и удалить строку, на которую ссылаются.
• В SQLite внешние ключи работают только после PRAGMA foreign_keys = ON; — в каждом подключении.
• ON DELETE CASCADE удаляет зависимые строки вместе с главной — осторожно.
• CREATE INDEX ускоряет поиск по столбцу, но замедляет запись; проверка — EXPLAIN QUERY PLAN.

Проверьте себя