Основы управления данными: роль первичных и внешних ключей в MySQL

В статье подробно рассказываем, какие key SQL используются и за что конкретно отвечает каждый из них. 

Будем разговаривать о первичном (Primary Key) и внешнем (Foreign Key) ключах. Каждый из них выполняет свои задачи и сильно упрощает работу с реляционными базами данных. 

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

Primary Key, или первичный ключ — это поле в SQL-таблице, которое позволяет идентифицировать имеющиеся записи. Какие данные могут быть ключом? Любые уникальные идентификаторы всех объектов, представленных в таблице. 

Есть разные сценарии применения первичного ключа. Например, этот инструмент позволяет: 

• упростить поиск данных — идентификатор позволяет быстро находить нужные записи;

• ускорить выполнение пользовательских запросов при больших объемах данных — уникальные идентификаторы позволяют быстро искать информацию и формировать выборки по определенным критериям;

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

Свойства первичного ключа в БД:

• в одной таблице есть только один первичный ключ, но он может быть составным;

• значение первичного ключа не бывает пустым, поскольку записи в таблице должны иметь уникальный идентификатор; 

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

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

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

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

Задачи Foreign Key в MySQL:

• поддержание ссылочной точности внутри базы данных — значение в столбце одной таблицы, которое ссылается на столбец в другой таблице, всегда будет актуально;

• легкое обновление записей — все изменения в родительской таблице могут автоматически отображаться в дочерней таблице, поэтому не придется ничего вносить вручную (если указано правило ON UPDATE CASCADE, о котором расскажем чуть позже); 

• обеспечение структурированности БД — будет явная связь между таблицами, благодаря чему проще понимать логику базы данных; 

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

Свойства внешнего ключа: 

• он может быть не единственным, если одну таблицу нужно связать с несколькими другими;

• внешний ключ не влияет на порядок хранения записей. Его задача — обеспечивать ссылочную целостность; 

• в некоторых случаях поле Foreign Key может содержать нулевые значения.

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

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

Создание первичного ключа при формировании таблицы

Создать Primary Key несложно — достаточно добавить в команду соответствующую строку. Это будет выглядеть примерно так: 

Добавление первичного ключа с помощью ALTER TABLE

С помощью этой команды можно вносить изменения в структуру таблиц, модифицировать, добавлять и удалять столбцы, присваивать индексы. Она используется и для добавления первичного ключа. Алгоритм: ALTER TABLE table_name ADD PRIMARY KEY (field_name).

ALTER TABLE позволяет не только добавлять, но и удалять ключ в MySQL. Команда: 

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

Создание внешнего ключа при формировании таблицы

Внешний ключ в MySQL можно создавать сразу, как только вы определитесь со структурой таблицы. Делается это по такой схеме: CREATE TABLE orders (order_id INT AUTO_INCREMENT, user_id INT, PRIMARY KEY (order_id), FOREIGN KEY (user_id) REFERENCES users(user_id). Здесь мы видим ссылку на первичный ключ в другой таблице. 

Каскадные операции и их применение

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

Возможные варианты: 

• CASCADE — каскадное удаление. Функция позволяет одномоментно удалять строки из родительской таблицы и связанные с ними строки в дочерних таблицах;

• RESTRICT — запрет изменений. Это опция позволяет отклонять запросы на модификации строк в родительской таблице при условии что в дочерней имеются связанные с ними строки. 

Например, если создать внешний ключ просто как FOREIGN KEY (CustomerId) REFERENCES Customers (Id) (без части ON DELETE...), то он будет работать именно как ON DELETE RESTRICT.

Вот примеры команд для этих опций. Разумеется, их нужно подстраивать под свои таблицы, вписывая реальные значения. 

Внешний ключ, как и любое ограничение (CONSTRAINT), можно удалить, если это потребуется при изменении структуры базы данных. Для этого используется команда ALTER TABLE с указанием имени ограничения:

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

Создание таблиц и ключей

Представьте, что нам нужно создать две таблицы — customers и orders. В первую внесем клиентов, во вторую — их заказы. 

Пошаговый план действий: 

• создаем таблицу клиентов с первичным ключом customer_id. Алгоритм: CREATE TABLE customers ( customer_id INT AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(100), PRIMARY KEY (customer_id) );

• создаем таблицу заказов с первичным ключом order_id. Алгоритм: CREATE TABLE orders ( order_id INT AUTO_INCREMENT, order_date DATE, customer_id INT, PRIMARY KEY (order_id) ). 

Реализация ссылочного соединения с помощью ALTER TABLE

Дальше нужно добавить связи — ссылочное соединение между двумя таблицами. В orders необходимо сделать колонку customer_id. Алгоритм: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id). Теперь дочерняя таблица связана с родительской через ключ. 

Проверка связи между двумя таблицами

Нужно убедиться, что изменения в родительской таблице будут отображаться в дочерней. Как это сделать: 

• создаем записи в таблице клиентов;

• добавляем записи в таблицу заказов, вносим в каждое поле customer_id соответствующее значение из родительской таблицы; 

• проверяем, корректно ли связаны записи. 

Если все сделано правильно, заказы в orders будут связаны с соответствующими поставщиками, указанными в customers. Когда информация обновится, изменения отразятся в обеих таблицах (если указано правило ON UPDATE CASCADE). Таким образом получается комплексная структура данных, в которой легко ориентироваться. 

Ключи применяются в базах данных с поддержкой MySQL. Такие БД изначально просты и удобны в управлении, а Primary key и Foreign key еще больше облегчают работу с ними. Механизм с ключами незаменим, если вы хотите обеспечить согласованность данных, при этом не нарушая их целостности. 

Выстраивать надежное администрирование с помощью ключей можно и в базе данных Advanced Relational Database Service for MySQL от Cloud.ru. Это масштабируемый сервис, который можно быстро развернуть и использовать для решения обширного пула задач даже при высоких нагрузках. 

А для комплексной работы с данными на всех этапах — от валидации и структурирования до анализа и визуализации — идеально подойдет Evolution Data Platform. Эта платформа объединяет управляемые сервисы для работы с данными, помогая:

• управлять большими объемами структурированной информации;

• выполнять сложные аналитические запросы к реляционным базам;

• строить отчеты с помощью встроенного BI-сервиса на основе актуальных данных, целостность которых обеспечивается в том числе за счет правильного использования ключей.

Ошибки обычно совершают пользователи, которые только начинают работать с ключами таблицы. Чаще всего проблемы возникают из-за нарушения правил, определяющих работу Primary key и Foreign key. Важно как можно скорее выявить отклонения, чтобы не допустить хаоса в записях. 

Распространенные ошибки при работе с первичными ключами и способы их предотвращения — в таблице. 

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