Первичный и внешний ключ в SQL: что это, как создать и чем отличаются

Первичный ключ (PRIMARY KEY) однозначно идентифицирует каждую строку таблицы: его значения уникальны и не бывают пустыми. Внешний ключ (FOREIGN KEY) лежит в другой таблице и ссылается на первичный ключ: СУБД не даст записать в столбец внешнего ключа значение, которого нет в родительской таблице.

Примеры запускались в SQLite 3.54 командой sqlite3 :memory: < example.sql, номер строки в ошибке считается от начала примера.

Что такое первичный ключ таблицы

Первичный ключ таблицы выбирают из столбцов, которые однозначно определяют запись: номер заказа, код товара. Такой ключ в таблице один, но он может состоять из нескольких столбцов (составной ключ).

Ключ бывает естественным (email, ИНН) и суррогатным: искусственный уникальный идентификатор id, который база выдаёт сама. Мы советуем суррогатный id: если ключом сделать email, после смены почты её придётся переписать во всех таблицах, которые на неё ссылаются. Почту при этом защищают от дублей ограничением UNIQUE.

В PostgreSQL и SQL Server для первичного ключа автоматически создаётся уникальный индекс, а в SQLite столбец INTEGER PRIMARY KEY становится псевдонимом rowid. Как индекс ускоряет поиск, разобрано в статье про индексы в SQL.

Что такое внешний ключ в SQL

Внешним ключом называют столбец (или несколько столбцов) дочерней таблицы, значения которого должны совпадать со значениями ключа родительской таблицы. В таблице orders столбец customer_id хранит id клиента из таблицы customers: клиенты вынесены в свою таблицу по правилам нормализации баз данных.

В SQLite и PostgreSQL внешний ключ ссылается только на первичный ключ или на столбцы с ограничением UNIQUE (уникальным индексом). В отличие от первичного, внешний ключ может содержать NULL: строка с пустым customer_id ограничение не нарушает, а запретить пустые значения можно через NOT NULL. Внешних ключей в одной таблице может быть несколько: заказ ссылается и на клиента, и на менеджера.

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

Чем отличается первичный ключ от внешнего

Создание первичного ключа в CREATE TABLE

Создаём таблицу клиентов. Столбец id INTEGER PRIMARY KEY в SQLite заполняется сам, если значение не указать. Вторая таблица проверяет текстовый ключ без NOT NULL.

CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);
INSERT INTO customers (name, email)
VALUES ('Анна', 'anna@example.com'), ('Борис', 'boris@example.com');
SELECT id, name FROM customers;
INSERT INTO customers (id, name, email) VALUES (2, 'Вера', 'vera@example.com');

-- текстовый ключ без NOT NULL
CREATE TABLE products (sku TEXT PRIMARY KEY, title TEXT);
INSERT INTO products VALUES (NULL, 'Клавиатура'), (NULL, 'Мышь');
SELECT count(*) FROM products WHERE sku IS NULL;

1|Анна
2|Борис
Error near line 9: UNIQUE constraint failed: customers.id
2

Анна и Борис получили id 1 и 2 автоматически, повтор id = 2 упал. Последняя строка показывает ловушку SQLite: по стандарту первичный ключ не допускает NULL, но SQLite его пропускает, если это не INTEGER PRIMARY KEY, таблица не STRICT и не WITHOUT ROWID, а столбец не объявлен NOT NULL. Два товара без sku легли в таблицу, поэтому текстовый и составной ключ в SQLite пишите с NOT NULL.

Создание внешнего ключа и проверка в SQLite

В SQLite проверка внешних ключей по умолчанию выключена, и включать её нужно в каждом соединении командой PRAGMA foreign_keys = ON. Без неё REFERENCES в CREATE TABLE ничего не проверяет:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    total       INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Анна'), (2, 'Борис');

PRAGMA foreign_keys;
INSERT INTO orders VALUES (101, 99, 1500);

PRAGMA foreign_keys = ON;
INSERT INTO orders VALUES (102, 99, 700);
PRAGMA foreign_key_check;

0
Error near line 13: FOREIGN KEY constraint failed
orders|101|customers|0

PRAGMA foreign_keys вернула 0, и заказ 101 на несуществующего клиента 99 записался. После включения проверки заказ 102 с тем же клиентом упал. Старые строки проверка не трогает. «Сироту» 101 нашла только PRAGMA foreign_key_check: дочерняя таблица orders, rowid строки, родительская таблица customers и номер нарушенного внешнего ключа таблицы (0, как в PRAGMA foreign_key_list).

Внутри открытой транзакции PRAGMA foreign_keys ничего не делает, а модуль sqlite3 в Python по умолчанию сам открывает транзакцию перед INSERT. На Python 3.14 после INSERT без commit() флаг так и остался 0. Включайте проверку сразу после connect().

Добавление ключа в существующую таблицу: ALTER TABLE

В PostgreSQL первичный ключ к готовой таблице добавляет ALTER TABLE orders ADD PRIMARY KEY (id);. Внешний ключ в PostgreSQL и MySQL добавляют так: ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id);. PostgreSQL при этом проверяет все строки таблицы. Опция NOT VALID откладывает проверку старых строк до команды VALIDATE CONSTRAINT.

В SQLite внешний ключ к готовой таблице добавляют только вместе с новым столбцом:

PRAGMA foreign_keys = ON;
CREATE TABLE managers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL);
INSERT INTO managers VALUES (1, 'Олег');
INSERT INTO orders VALUES (101, 1500), (102, 700);

ALTER TABLE orders ADD CONSTRAINT fk_orders_manager
    FOREIGN KEY (manager_id) REFERENCES managers (id);
ALTER TABLE orders ADD COLUMN manager_id INTEGER
    REFERENCES managers (id) ON DELETE SET NULL;

UPDATE orders SET manager_id = 1;
UPDATE orders SET manager_id = 5 WHERE id = 102;
DELETE FROM managers WHERE id = 1;
SELECT id, manager_id FROM orders;

Parse error near line 7: near "FOREIGN": syntax error
  TABLE orders ADD CONSTRAINT fk_orders_manager     FOREIGN KEY (manager_id) REF
                                      error here ---^
Error near line 13: FOREIGN KEY constraint failed
101|
102|

ADD CONSTRAINT ... FOREIGN KEY упал с синтаксической ошибкой. Текст ошибки здесь по клиенту SQLite 3.54, в других версиях он может отличаться: в SQLite 3.50 (модуль sqlite3 в Python) это near "CONSTRAINT". Новый столбец manager_id добавился и сразу проверяется: менеджера 5 нет, и обновление заказа 102 отклонено. После удаления Олега правило ON DELETE SET NULL очистило manager_id в обоих заказах. Первичный ключ и UNIQUE через ALTER TABLE в SQLite не добавить. Таблицу пересоздают: выключают PRAGMA foreign_keys, в транзакции создают новую таблицу с нужными ключами, переносят данные (INSERT ... SELECT), удаляют старую, переименовывают новую, восстанавливают индексы и триггеры, запускают PRAGMA foreign_key_check, фиксируют транзакцию и включают проверку обратно.

ON DELETE и ON UPDATE: CASCADE, SET NULL, RESTRICT

ON DELETE говорит, что делать с дочерними строками при удалении родительской строки, а ON UPDATE срабатывает при изменении её ключа. Вариантов пять:

• NO ACTION (по умолчанию): удаление или изменение ключа, на который есть ссылки, заканчивается ошибкой;
• RESTRICT: тоже запрет (отличие в разделе ниже);
• CASCADE: дочерние строки удаляются вместе с родительской, при ON UPDATE получают новое значение ключа;
• SET NULL: во внешний ключ пишется NULL;
• SET DEFAULT: пишется значение по умолчанию (таблицы InnoDB в MySQL это правило не принимают).

PRAGMA foreign_keys = ON;
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products  (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id)
                ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE order_items (
    order_id   INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products (id),
    quantity   INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES (1, 'Анна'), (2, 'Борис');
INSERT INTO products VALUES (10, 'Клавиатура'), (11, 'Мышь');
INSERT INTO orders VALUES (101, 1), (102, 2);
INSERT INTO order_items VALUES (101, 10, 1), (101, 11, 2), (102, 11, 1);

INSERT INTO order_items VALUES (101, 10, 5);
DELETE FROM customers WHERE id = 1;
UPDATE customers SET id = 20 WHERE id = 2;
SELECT id, customer_id FROM orders;
DELETE FROM orders WHERE id = 101;
SELECT order_id, product_id FROM order_items;

Error near line 20: UNIQUE constraint failed: order_items.order_id, order_items.product_id
Error near line 21: FOREIGN KEY constraint failed
101|1
102|20
102|11

Первую ошибку дал составной ключ PRIMARY KEY (order_id, product_id): пара «заказ 101, товар 10» уже есть. Удаление Анны отклонил RESTRICT. Смена id Бориса на 20 через ON UPDATE CASCADE переписала customer_id в заказе 102. Удаление заказа 101 через ON DELETE CASCADE забрало обе его позиции.

Каскадное удаление удобно для частей целого вроде позиций заказа. На связь клиента с его заказами мы бы его не ставили: одна команда DELETE тихо сотрёт историю покупок.

RESTRICT и NO ACTION: в чём разница

Оба правила запрещают удалить строку, на которую есть ссылки. RESTRICT срабатывает сразу, на самой операции. NO ACTION проверяет ограничение в конце команды, а у отложенного ключа (DEFERRABLE INITIALLY DEFERRED) при COMMIT: до этого другие команды транзакции успевают всё исправить.

PRAGMA foreign_keys = ON;
CREATE TABLE customers (id INTEGER PRIMARY KEY);
CREATE TABLE orders_a (customer_id INTEGER REFERENCES customers (id)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE orders_r (customer_id INTEGER REFERENCES customers (id)
    ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED);
INSERT INTO customers VALUES (1);
INSERT INTO orders_a VALUES (1);

-- NO ACTION: удаляем клиента и возвращаем до COMMIT
BEGIN;
DELETE FROM customers WHERE id = 1;
INSERT INTO customers VALUES (1);
COMMIT;
SELECT 'NO ACTION: COMMIT прошёл';

-- RESTRICT: тот же DELETE
INSERT INTO orders_r VALUES (1);
BEGIN;
DELETE FROM customers WHERE id = 1;
ROLLBACK;

NO ACTION: COMMIT прошёл
Error near line 20: FOREIGN KEY constraint failed

С отложенным NO ACTION клиента удалили и вернули до COMMIT, и проверка прошла; если клиента не вернуть, упадёт уже COMMIT.

RESTRICT отказал сразу на DELETE, хотя ключ тоже отложенный.

В PostgreSQL разница та же: RESTRICT не даёт отложить проверку, а NO ACTION позволяет другим командам исправить ситуацию. В MySQL InnoDB NO ACTION равен RESTRICT, операция отклоняется сразу. В списке действий SQL Server слова RESTRICT нет.

Типичные ошибки с ключами

Ошибка foreign key mismatch всплывает не там, где сделана. Таблица payments ссылается на customers.email без UNIQUE:

PRAGMA foreign_keys = ON;
CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT);
CREATE TABLE payments (email TEXT REFERENCES customers (email));
INSERT INTO customers VALUES (1, 'anna@example.com');
INSERT INTO payments VALUES ('anna@example.com');
DELETE FROM customers WHERE id = 1;

Parse error near line 5: foreign key mismatch - "payments" referencing "customers"
Parse error near line 6: foreign key mismatch - "payments" referencing "customers"

Таблица создалась молча, а ошибка появилась на вставке в payments и на удалении клиента.

Ещё одна ошибка не нарушает целостность: внешний ключ без индекса. SQLite, PostgreSQL и SQL Server индекс на столбец внешнего ключа сами не создают. Изменения первичного ключа СУБД сверяет с дочерней таблицей, и индекс помогает быстро найти в ней связанные строки. На orders.customer_id ставят обычный индекс: CREATE INDEX idx_orders_customer ON orders (customer_id);. MySQL InnoDB создаёт такой индекс сам, если подходящего нет.

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

Может ли таблица ссылаться сама на себя?

Да. В таблице employees столбец manager_id может ссылаться на id той же таблицы, а у руководителя верхнего уровня в manager_id будет NULL.

Сколько первичных ключей может быть в таблице?

Один. Если строку определяют несколько столбцов, из них собирают один составной ключ, а другие уникальные поля объявляют через UNIQUE. Потренироваться на похожей схеме можно на задачах по SQL.