Первичный и внешний ключ в базе данных: что это такое и как работает

Что такое первичный ключ (Primary Key)

Первичный ключ — поле или набор полей, однозначно идентифицирующих каждую строку таблицы. Три базовых свойства: уникальность значений (дубли невозможны), запрет NULL-значений даже без явного NOT NULL и строго один PK на таблицу. В реальных схемах роль первичного ключа чаще всего играют числовые идентификаторы — customer_id, order_id, product_id. При объявлении PK СУБД автоматически создаёт кластерный индекс таблицы — это ускоряет поиск записей и JOIN-запросы.

Синтаксис и AUTO_INCREMENT: как задать первичный ключ

Простейший вариант — объявить PK прямо в теле CREATE TABLE:

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL

);

Именованное ограничение удобнее при командной разработке и отладке:

CREATE TABLE users (

id INT NOT NULL,

name VARCHAR(100),

CONSTRAINT pk_users PRIMARY KEY (id)

);

Добавить или удалить первичный ключ постфактум:

ALTER TABLE users ADD PRIMARY KEY (id);

ALTER TABLE users DROP PRIMARY KEY;

В PostgreSQL вместо AUTO_INCREMENT используется SERIAL (автоинкрементные значения для небольших таблиц) или BIGSERIAL для больших: id SERIAL PRIMARY KEY.

Составной первичный ключ: когда одного столбца недостаточно

Составной первичный ключ охватывает два и более столбца — уникальной считается именно их совместная комбинация, а не каждый столбец отдельно. Синтаксис: PRIMARY KEY (col1, col2). Типичный сценарий — таблицы связей «многие ко многим»: PRIMARY KEY (order_id, product_id) — это один составной ключ, а не два отдельных.

Что такое внешний ключ (Foreign Key)

Внешний ключ — атрибут дочерней таблицы, ссылающийся на первичный ключ родительской. Если в таблице orders поле customer_id объявлено как FK на customers.customer_id, СУБД при каждой вставке строки проверяет: такой клиент должен существовать. Это и есть ссылочная целостность — состояние базы данных без «осиротевших» записей. В отличие от PK, внешний ключ допускает NULL-значения (запись без ссылки на родительскую таблицу допустима) и может встречаться несколько раз в одной таблице.

Освоить проектирование реляционных баз данных с нуля позволяет программа «Специалист по аналитике и базам данных в информационных системах» — 72 часа онлайн, бесплатно в рамках нацпроекта «Кадры». Подробнее — в каталоге программ.

Синтаксис внешнего ключа: CREATE TABLE и ALTER TABLE

Объявление FK при создании таблицы:

CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,

customer_id INT,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

Именованное ограничение через CONSTRAINT — рекомендуется, так как упрощает последующее удаление:

CONSTRAINT fk_orders_customer

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

Добавление и удаление FK через ALTER TABLE:

ALTER TABLE orders

ADD CONSTRAINT fk_orders_customer

FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

Движок InnoDB и автоматический индекс FK

В MySQL внешние ключи поддерживает только движок хранения InnoDB — стандарт с версии MySQL 5.5. Таблицы на MyISAM принимают синтаксис FOREIGN KEY без ошибок, но игнорируют ограничение. При добавлении FK MySQL автоматически создаёт индекс внешнего ключа, который задействован при каждой проверке ссылочной целостности.

Первичный ключ и внешний ключ: в чём разница

Сравнительная таблица по семи критериям — удобная точка отсчёта при проектировании реляционной базы данных и выборе нужного инструмента.

PK идентифицирует запись внутри своей таблицы, FK задаёт связь между таблицами. Вместе они обеспечивают согласованность данных и превращают разрозненные таблицы в целостную реляционную структуру.

Каскадные операции: ON DELETE и ON UPDATE

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

FOREIGN KEY (customer_id)

REFERENCES customers(customer_id)

ON DELETE CASCADE

ON UPDATE SET NULL

Если ON DELETE или ON UPDATE не указаны, MySQL по умолчанию применяет RESTRICT.

Практика: создаём связанные таблицы в MySQL

Полный пример: родительская таблица customers с первичным ключом и дочерняя orders с FK:

CREATE TABLE customers (

customer_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL

);

CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,

customer_id INT,

amount DECIMAL(10,2),

CONSTRAINT fk_orders_customer

FOREIGN KEY (customer_id)

REFERENCES customers(customer_id)

ON DELETE RESTRICT

);

Проверить структуру таблицы помогут DESC orders или SHOW CREATE TABLE orders — второй вариант показывает полный синтаксис вместе со всеми ограничениями.

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

Пять ошибок, с которыми сталкиваются чаще всего:

• Дублирование PK — INSERT с уже существующим значением id. Решение: использовать AUTO_INCREMENT или проверять id перед вставкой.
• Несовпадение типов данных — FK объявлен как INT, PK родительской таблицы — BIGINT. Решение: типы и знаковость должны совпадать точно.
• FK на таблице MyISAM — синтаксис принимается, ограничение игнорируется. Решение: переключить движок хранения на InnoDB.
• Удаление PK при активных FK-ссылках — MySQL заблокирует операцию. Решение: сначала удалить FK во всех дочерних таблицах.
• INSERT с несуществующим FK-значением — нарушение ссылочной целостности. Решение: сначала добавить соответствующую строку в родительскую таблицу.

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

В Microsoft Access первичные и внешние ключи создаются через графический интерфейс — без SQL-синтаксиса. Инструмент ориентирован на бухгалтеров, студентов и малый бизнес. Чтобы задать первичный ключ: откройте таблицу в режиме конструктора, выделите нужное поле и нажмите «Ключевое поле» на панели. При создании новой таблицы Access автоматически предлагает поле-счётчик (аналог AUTO_INCREMENT) в роли первичного ключа.

Для настройки FK используется «Схема данных»: вкладка «Работа с базами данных» → «Схема данных» → перетащите поле из родительской таблицы на соответствующее поле дочерней. В открывшемся диалоге можно включить обеспечение ссылочной целостности и задать каскадное обновление или удаление — всё без единой строки кода.

Часто задаваемые вопросы

Что такое первичный и внешний ключ в SQL — кратко?

Первичный ключ (PK) — уникальный идентификатор строки таблицы: запрещает NULL и дублирование, один на таблицу. Внешний ключ (FK) — поле дочерней таблицы, ссылающееся на PK родительской. FK обеспечивает ссылочную целостность: нельзя добавить FK-значение без соответствующей записи в родительской таблице. Вместе PK и FK — основа реляционной модели данных.

Может ли внешний ключ содержать NULL?

Да. В отличие от первичного ключа, внешний ключ допускает NULL-значения — запись в дочерней таблице может существовать без ссылки на родительскую. Например, заказ без привязки к клиенту, если customer_id в orders не заполнен. Но если FK-поле имеет значение, оно обязано совпадать с существующим PK в родительской таблице.

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

Ровно один — это фундаментальное ограничение реляционной модели. PK может быть составным и включать несколько столбцов, но таблица имеет только один первичный ключ. Например, PRIMARY KEY (order_id, product_id) — один составной ключ, а не два. Количество внешних ключей в одной таблице при этом не ограничено.

Может ли поле быть одновременно первичным и внешним ключом?

Да, это распространённая практика в таблицах связей «многие ко многим». Оба столбца такой таблицы образуют составной первичный ключ и одновременно являются внешними ключами, ссылающимися на разные родительские таблицы. Это стандартный результат нормализации базы данных при соответствующем типе отношений.

Что произойдёт при удалении записи, на которую ссылается внешний ключ?

Результат зависит от опции ON DELETE. При RESTRICT (по умолчанию) MySQL заблокирует удаление, если в дочерней таблице есть ссылающиеся строки. При CASCADE дочерние записи удалятся автоматически. При SET NULL FK-поле дочерней записи получит значение NULL. Опцию задают при создании FK: ON DELETE CASCADE.

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

PK уникально идентифицирует строку в своей таблице: не допускает NULL и дублей, один на таблицу. FK хранит ссылку на PK другой таблицы: значения могут повторяться, NULL допустим, несколько FK в одной таблице — норма. В схеме «клиенты–заказы»: customer_id в customers — PK, тот же customer_id в orders — FK.

Что такое первичный и внешний ключ в базе данных — суть в двух словах?

PK задаёт уникальность: каждая строка получает неповторимый идентификатор. FK задаёт связь: дочерняя таблица через FK «знает» о родительской. Вместе они превращают набор отдельных таблиц в реляционную структуру, где данные согласованы и не дублируются — это и есть цель проектирования базы данных.

Нужен ли отдельный индекс для столбца внешнего ключа?

MySQL создаёт индекс для FK-столбца автоматически при добавлении ограничения FOREIGN KEY. Индекс используется при каждом INSERT, UPDATE и DELETE для проверки ссылочной целостности. Удалять его вручную не рекомендуется: это снизит производительность JOIN-запросов и операций каскадирования без какой-либо выгоды.