Введение
Одной из ценных особенностей реляционных баз данных является форматирование данных в четко определенной структуре. Эта структура достигается за счет использования таблиц с фиксированными столбцами, соблюдения четко определенных типов данных и обеспечения одинакового формата каждой строки. При хранении данных в виде строк в таблицах крайне важно иметь возможность однозначно находить и ссылаться на них. В языке структурированных запросов (SQL) это достигается с помощью первичных ключей, которые выступают в качестве идентификаторов отдельных строк в таблицах реляционной базы данных.
В этом руководстве вы узнаете о первичных ключах и о том, как использовать несколько различных типов для идентификации уникальных строк в таблицах базы данных. Используя несколько примеров наборов данных, вы создадите первичные ключи по одному столбцу, нескольким столбцам и автоинкрементным последовательным ключам.
Предпосылки
Для работы с этим руководством вам понадобится компьютер с установленной системой управления реляционными базами данных (СУРБД) на базе SQL. Инструкции и примеры в этом руководстве были проверены в следующей среде:
- Сервер под управлением Ubuntu 20.04 с пользователем без прав root, обладающим правами администратора, и брандмауэром, настроенным с помощью UFW.
- MySQL установлен и защищен на сервере.
- Базовое введение в выполнение запросов SELECT для извлечения данных из базы данных
Примечание: Обратите внимание, что многие СУБД используют собственную уникальную реализацию SQL. Хотя команды, упомянутые в этом руководстве, работают в большинстве СУБД, а первичные ключи являются частью стандарта SQL, некоторые функции являются специфичными для конкретной базы данных, поэтому точный синтаксис или вывод могут отличаться, если вы тестируете их в системе, отличной от MySQL.
Вам также потребуется пустая база данных, в которой вы сможете создать таблицы, используя первичные ключи. Мы рекомендуем вам ознакомиться с разделом «Подключение к MySQL и настройка тестовой базы данных» ниже, чтобы получить подробную информацию о подключении к серверу MySQL и создании тестовой базы данных, используемой в примерах этого руководства.
Подключение к MySQL и настройка образца базы данных
В этом разделе вы подключитесь к серверу MySQL и создадите экземпляр базы данных, чтобы затем использовать примеры из этого руководства.
Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локальной машины:
ssh sammy@your_server_ipЗатем откройте командную строку сервера MySQL и замените Sami на свое имя пользователя MySQL:
mysql -u sammy -p
Создайте базу данных с именем primary_keys:
CREATE DATABASE primary_keys;
Если база данных была создана успешно, вы получите следующий вывод:
Output
Query OK, 1 row affected (0.01 sec)Для выбора базы данных primary_keys выполните следующую инструкцию USE:
USE primary_keys;
Вы получите следующий результат:
Output
Database changedПосле выбора базы данных вы можете создать в ней тестовые таблицы. Теперь вы готовы следовать остальной части руководства и начать работать с первичными ключами в MySQL.
Введение в мажорные тональности
Данные в реляционной базе данных хранятся в таблицах со специфической, единообразной структурой отдельных строк. Определение таблицы описывает, какие столбцы существуют и какие типы данных могут храниться в отдельных столбцах. Этого достаточно для хранения информации в базе данных и поиска её с использованием различных критериев фильтрации с помощью предложения WHERE. Однако такая структура не гарантирует однозначного поиска каждой строки.
Представьте себе базу данных всех зарегистрированных транспортных средств, которым разрешено передвигаться по дорогам общего пользования. База данных содержит такую информацию, как марка, модель, год выпуска и цвет кузова. Однако, если бы вы искали красный Chevrolet Camaro 2007 года выпуска, вы могли бы найти не один. В конце концов, автопроизводители продают одни и те же автомобили нескольким покупателям. Именно поэтому зарегистрированные автомобили имеют номерные знаки, которые идентифицируют каждое транспортное средство. Если вы ищете автомобиль с номерным знаком OFP857, вы можете быть уверены, что этот критерий найдет только один автомобиль. Это потому, что по закону номерные знаки однозначно идентифицируют зарегистрированные автомобили. В реляционной базе данных такой элемент данных называется первичным ключом.
Первичные ключи — это уникальные идентификаторы, находящиеся в столбце или наборе столбцов, которые позволяют однозначно идентифицировать каждую строку в таблице базы данных. Несколько правил отражают технические характеристики первичных ключей:
- Первичный ключ должен использовать уникальные значения. Если первичный ключ состоит из более чем одного столбца, комбинация значений в этих столбцах должна быть уникальной для всей таблицы. Поскольку ключ используется для однозначной идентификации каждой строки, он не может встречаться более одного раза.
- Первичный ключ не должен содержать значений NULL.
- В каждой таблице базы данных может использоваться только один первичный ключ.
Механизм базы данных обеспечивает соблюдение этих правил, поэтому, если в таблице определен первичный ключ, вы можете быть уверены в корректности его атрибутов.
Помимо этих технических характеристик, следует также учитывать содержание данных, чтобы решить, какой тип данных лучше всего подходит в качестве первичного ключа. Естественные ключи — это идентификаторы, которые уже существуют в наборе данных, тогда как суррогатные ключи — это искусственные идентификаторы.
В некоторых структурах данных первичные ключи встречаются в наборе данных естественным образом, например, номера номерных знаков в базе данных транспортных средств или номера социального страхования в реестре граждан США. Иногда такие идентификаторы представляют собой не одно значение, а пару или комбинацию нескольких значений. Например, в местном городском справочнике домов название улицы или номер дома сами по себе не могут однозначно идентифицировать дом. На одной улице может быть несколько домов, и один и тот же номер может встречаться на нескольких улицах. Но пара названий улиц и номеров домов может считаться уникальным идентификатором дома. Такие естественные идентификаторы называются естественными ключами.
Однако часто данные не могут быть однозначно идентифицированы значениями одного столбца или небольшого подмножества столбцов. В этом случае создаются искусственные первичные ключи, например, с использованием последовательности чисел или случайно сгенерированных идентификаторов, таких как UUID. Такие ключи называются суррогатными ключами.
В следующих разделах вы создадите естественные ключи на основе одного или нескольких столбцов, а также создадите альтернативные ключи в таблицах, где использование естественного ключа невозможно.
Создайте первичный ключ для столбца.
Во многих случаях набор данных естественным образом включает столбец, который можно использовать для однозначной идентификации строк в таблице. В таких случаях можно создать естественный ключ для описания данных. Продолжая предыдущий пример с базой данных зарегистрированных автомобилей, представьте себе таблицу со следующей структурой:
Sample table
+---------------+-----------+------------+-------+------+
| license_plate | brand | model | color | year |
+---------------+-----------+------------+-------+------+
| ABC123 | Ford | Mustang | Red | 2018 |
| CES214 | Ford | Mustang | Red | 2018 |
| DEF456 | Chevrolet | Camaro | Blue | 2016 |
| GHI789 | Dodge | Challenger | Black | 2014 |
+---------------+-----------+------------+-------+------+Первая и вторая строки описывают красный Ford Mustang 2018 года выпуска. Идентифицировать автомобиль по марке и модели невозможно. Номерной знак в обоих случаях отличается и служит хорошим уникальным идентификатором для каждой строки в таблице. Поскольку номерной знак уже является частью данных, использование его в качестве первичного ключа создает естественный ключ. Если бы вы создали таблицу без использования первичного ключа для столбца License_plate, вы бы рисковали столкнуться с появлением дублирующейся или пустой страницы в наборе данных.
Далее создайте таблицу, аналогичную приведенной выше, с столбцом License_plate в качестве первичного ключа и следующими столбцами:
- License_plate: В этом столбце содержится номерной знак автомобиля, представленный типом данных varchar.
- Марка: В этом столбце представлена марка автомобиля, указанная с использованием типа данных varchar с максимальным количеством символов 50.
- Модель: В этом столбце хранится модель автомобиля, представленная в формате varchar с максимальным количеством символов 50.
- Цвет: В этом столбце хранится цвет, выраженный с использованием типа данных varchar, с максимальным количеством символов 20.
- год: В этом столбце отображается год выпуска автомобиля, выраженный с использованием типа данных int для хранения числовых данных.
Для создания таблицы машин выполните следующий SQL-запрос:
CREATE TABLE cars (
license_plate varchar(8) PRIMARY KEY,
brand varchar(50),
model varchar(50),
color varchar(20),
year int
);... */Оператор PRIMARY KEY соответствует определению типа данных License_plate. При работе с первичными ключами, основанными на одном столбце, можно использовать упрощенный синтаксис для создания ключа и указать первичный ключ в определении столбца.
Если напечатан следующий вывод, таблица создана:
Output
Query OK, 0 rows affected (0.00 sec)После этого загрузите в таблицу строки из приведенного выше примера, выполнив следующую операцию INSERT INTO:
INSERT INTO cars VALUES
('ABC123', 'Ford', 'Mustang', 'Red', 2018),
('CES214', 'Ford', 'Mustang', 'Red', 2018),
('DEF456', 'Chevrolet', 'Camaro', 'Blue', 2016),
('GHI789', 'Dodge', 'Challenger', 'Black', 2014);База данных ответит сообщением об успешном завершении:
Output
Query OK, 4 rows affected (0.010 sec)
Records: 4 Duplicates: 0 Warnings: 0Теперь вы можете убедиться, что созданная таблица содержит ожидаемые данные и формат, используя оператор SELECT:
SELECT * FROM cars;
В результате отображается таблица, аналогичная той, что приведена в начале раздела:
Output
+---------------+-----------+------------+-------+------+
| license_plate | brand | model | color | year |
+---------------+-----------+------------+-------+------+
| ABC123 | Ford | Mustang | Red | 2018 |
| CES214 | Ford | Mustang | Red | 2018 |
| DEF456 | Chevrolet | Camaro | Blue | 2016 |
| GHI789 | Dodge | Challenger | Black | 2014 |
+---------------+-----------+------------+-------+------+Далее вы можете проверить, гарантируются ли правила первичного ключа механизмом базы данных. Попробуйте вставить автомобиль с повторяющимся номерным знаком, выполнив следующую команду:
INSERT INTO cars VALUES ('DEF456', 'Jeep', 'Wrangler', 'Yellow', 2019);
MySQL выдает сообщение об ошибке, указывающее на то, что тег DEF456 приводит к дублированию записи для первичного ключа:
Output ERROR 1062 (23000): Duplicate entry 'DEF456' for key 'cars.PRIMARY'
Примечание: по сути, первичные ключи реализуются с помощью уникальных индексов и обладают многими теми же свойствами, что и индексы, которые вы могли бы создавать вручную для других столбцов таблицы. Что наиболее важно, индексы первичных ключей также повышают производительность запросов к таблице по столбцу, для которого определен индекс. Для получения дополнительной информации об использовании индексов для этой цели см. руководство «Как использовать индексы» в этом руководстве.
Теперь вы можете быть уверены, что дубликаты номерных знаков не допускаются. Далее проверьте, можно ли ввезти автомобиль с пустым номерным знаком:
INSERT INTO cars VALUES (NULL, 'Jeep', 'Wrangler', 'Yellow', 2019);
На этот раз база данных выдаст другое сообщение об ошибке:
Output
ERROR 1048 (23000): Column 'license_plate' cannot be nullБлагодаря этим двум правилам, применяемым базой данных, вы можете быть уверены, что License_plate однозначно идентифицирует каждую строку в таблице. Если вы выполните запрос к таблице по каждому номерному знаку, вы можете ожидать, что каждый раз получите одну строку.
В следующем разделе вы узнаете, как использовать первичные ключи с несколькими столбцами.
Создание первичного ключа для нескольких столбцов
Если одного столбца недостаточно для однозначной идентификации строки в таблице, можно создать первичные ключи, использующие более одного столбца.
Например, представьте себе реестр домов, где ни названия улицы, ни номера дома недостаточно для идентификации каждого дома:
Sample table
+-------------------+---------------+-------------------+------+
| street_name | street_number | house_owner | year |
+-------------------+---------------+-------------------+------+
| 5th Avenue | 100 | Bob Johnson | 2018 |
| Broadway | 1500 | Jane Smith | 2016 |
| Central Park West | 100 | John Doe | 2014 |
| Central Park West | 200 | Tom Thompson | 2015 |
| Lexington Avenue | 5001 | Samantha Davis | 2010 |
| Park Avenue | 7000 | Michael Rodriguez | 2012 |
+-------------------+---------------+-------------------+------+Название улицы Central Park West встречается в таблице несколько раз, как и номер дома 100. Однако повторяющихся пар «название улицы — номер дома» не обнаружено. В этом случае, хотя ни один из столбцов не может быть первичным ключом, пара этих двух значений может использоваться для однозначной идентификации каждой строки в таблице.
Далее создайте таблицу, аналогичную показанной выше, со следующими столбцами:
- street_name: В этом столбце представлено название улицы, на которой расположен дом, тип данных varchar, ограниченный 50 символами.
- street_number: В этом столбце хранится номер дома, представленный типом данных varchar. Столбец может содержать до 5 символов. Он не использует тип данных numeric int, поскольку некоторые номера домов могут содержать буквы (например, 200B).
- house_owner: В этом столбце содержится имя владельца дома, представленное типом данных varchar, ограниченным 50 символами.
- год: В этом столбце указан год постройки дома, представленный целочисленным типом данных для хранения числовых значений.
В этот раз в качестве первичного ключа используются столбцы street_name и street_number, а не только один из них. Для этого выполните следующий SQL-запрос:
CREATE TABLE houses (
street_name varchar(50),
street_number varchar(5),
house_owner varchar(50),
year int,
PRIMARY KEY(street_name, street_number)
);В этот раз, в отличие от предыдущего примера, оператор PRIMARY KEY расположен ниже определений столбцов. Оператор PRIMARY KEY заключен в скобки с двумя именами столбцов: street_name и street_number. Такой синтаксис создает первичный ключ в таблице houses, который расположен в двух столбцах.
Если напечатан следующий вывод, таблица создана:
Output
Query OK, 0 rows affected (0.00 sec)После этого загрузите в таблицу строки из предыдущего примера, выполнив следующую операцию INSERT INTO:
INSERT INTO houses VALUES
('Central Park West', '100', 'John Doe', 2014),
('Broadway', '1500', 'Jane Smith', 2016),
('5th Avenue', '100', 'Bob Johnson', 2018),
('Lexington Avenue', '5001', 'Samantha Davis', 2010),
('Park Avenue', '7000', 'Michael Rodriguez', 2012),
('Central Park West', '200', 'Tom Thompson', 2015);
База данных ответит сообщением об успешном завершении:
Output
Query OK, 6 rows affected (0.000 sec)
Records: 6 Duplicates: 0 Warnings: 0Теперь вы можете убедиться, что созданная таблица содержит ожидаемые данные и формат, используя оператор SELECT:
SELECT * FROM houses;
В результате отображается таблица, аналогичная той, что приведена в начале раздела:
Output
+-------------------+---------------+-------------------+------+
| street_name | street_number | house_owner | year |
+-------------------+---------------+-------------------+------+
| 5th Avenue | 100 | Bob Johnson | 2018 |
| Broadway | 1500 | Jane Smith | 2016 |
| Central Park West | 100 | John Doe | 2014 |
| Central Park West | 200 | Tom Thompson | 2015 |
| Lexington Avenue | 5001 | Samantha Davis | 2010 |
| Park Avenue | 7000 | Michael Rodriguez | 2012 |
+-------------------+---------------+-------------------+------+
6 rows in set (0.000 sec)Теперь давайте проверим, допускает ли база данных строки с повторяющимися названиями улиц и номерами домов, но запрещает появление в таблице повторяющихся полных адресов. Начнем с добавления еще одного дома на Парк-стрит:
INSERT INTO houses VALUES ('Park Avenue', '8000', 'Emily Brown', 2011);
MySQL выдает сообщение об успешном выполнении, поскольку адрес 8000 Park Avenue ранее не фигурировал в таблице:
Output
Query OK, 1 row affected (0.010 sec)Аналогичный результат получается, если добавить дом по адресу 8000 Main Street и повторить номер дома:
INSERT INTO houses VALUES ('Main Street', '8000', 'David Jones', 2009);
И снова, это корректно вставляет новую строку, поскольку весь адрес не повторяется:
Output
Query OK, 1 row affected (0.010 sec)Однако добавьте еще один дом по адресу 100 5th Avenue, используя следующую инструкцию INSERT:
INSERT INTO houses VALUES ('5th Avenue', '100', 'Josh Gordon', 2008);
База данных выдает сообщение об ошибке, указывающее на наличие повторяющейся записи для первичного ключа в виде пары значений «5th Avenue» и «100»:
Output
ERROR 1062 (23000): Duplicate entry '5th Avenue-100' for key 'houses.PRIMARY'База данных корректно применяет правила первичного ключа, определяя ключ на основе пары столбцов. Вы можете быть уверены, что полный адрес, включая название улицы и номер дома, не будет дублироваться в таблице.
В этом разделе вы создали естественный ключ с парой столбцов для уникальной идентификации каждой строки в таблице home. Однако первичные ключи не всегда можно извлечь из набора данных. В следующем разделе вы будете использовать искусственные первичные ключи, которые не берутся непосредственно из данных.
Создание последовательного первичного ключа
До сих пор вы создавали уникальные первичные ключи, используя столбцы из примера набора данных. Но в некоторых случаях данные неизбежно дублируются, что делает столбцы непригодными в качестве уникальных идентификаторов. В таких случаях вы можете создать последовательные первичные ключи, используя сгенерированные идентификаторы. Когда ваши данные требуют создания новых идентификаторов для идентификации строк, первичные ключи, созданные на основе этих искусственных идентификаторов, называются суррогатными ключами.
Представьте список членов книжного клуба — неформального собрания, куда может присоединиться любой желающий, не предъявляя удостоверение личности. Вполне вероятно, что в какой-то момент к клубу присоединятся люди с похожими именами:
Sample table
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Jane | Smith |
| Bob | Johnson |
| Samantha | Davis |
| Michael | Rodriguez |
| Tom | Thompson |
| Sara | Johnson |
| David | Jones |
| Jane | Smith |
| Bob | Johnson |
+------------+-----------+В таблице повторяются имена Боб Джонсон и Джейн Смит. Для уточнения личности необходимо использовать дополнительный идентификатор, а однозначно идентифицировать строки в этой таблице невозможно. Если бы вы вели список членов книжного клуба на бумаге, вы могли бы использовать вспомогательные идентификаторы для различения людей с похожими именами в группе.
В реляционной базе данных можно сделать нечто подобное, используя дополнительный столбец, содержащий сгенерированные, нереальные идентификаторы, единственная цель которых — однозначно изолировать все строки в таблице. Назовем его Member_id.
Однако создавать такой идентификатор каждый раз, когда вы хотите добавить в базу данных еще одного участника книжного клуба, довольно неудобно. Для решения этой проблемы MySQL предоставляет функцию автоматического увеличения значений числовых столбцов, при которой база данных автоматически увеличивает значение столбца на последовательность целых чисел.
Давайте создадим таблицу, похожую на приведенную выше. Добавим дополнительный столбец с автоинкрементом (member_id) для хранения автоматически присваиваемого номера каждому члену клуба. Этот автоматически присваиваемый номер будет служить первичным ключом для таблицы:
Member_id: В этом столбце содержится автоматически увеличивающийся числовой идентификатор, представленный типом данных int.
first_name: Этот столбец содержит имя члена клуба, представленное типом данных varchar, ограниченным 50 символами.
last_name: В этом столбце хранятся фамилии членов клуба, отображаемые с использованием типа данных varchar, ограниченного 50 символами.
Для создания таблицы выполните следующий SQL-запрос:
CREATE TABLE club_members (
member_id int AUTO_INCREMENT PRIMARY KEY,
first_name varchar(50),
last_name varchar(50)
);Хотя оператор PRIMARY KEY располагается после определения типа столбца, как и в случае с одностолбцовым первичным ключом, перед ним появляется дополнительный атрибут: AUTO_INCREMENT. Он указывает MySQL автоматически генерировать значения для этого столбца, если они не указаны явно, используя возрастающую последовательность чисел.
Примечание: Свойство AUTO_INCREMENT для определений столбцов является специфичным для MySQL. Другие базы данных часто предоставляют аналогичные методы для генерации последовательных ключей, но синтаксис различается в зависимости от движка. В случае сомнений рекомендуем обратиться к официальной документации вашей СУБД.
Если напечатан следующий вывод, таблица создана:
Output
Query OK, 0 rows affected (0.00 sec)После этого загрузите в таблицу строки из приведенного выше примера, выполнив следующую операцию INSERT INTO:
INSERT INTO club_members (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Bob', 'Johnson'),
('Samantha', 'Davis'),
('Michael', 'Rodriguez'),
('Tom', 'Thompson'),
('Sara', 'Johnson'),
('David', 'Jones'),
('Jane', 'Smith'),
('Bob', 'Johnson');Теперь оператор INSERT включает список имен столбцов (first_name и last_name), что гарантирует, что база данных знает, что столбец Member_id отсутствует в наборе данных, поэтому вместо него следует использовать значение по умолчанию.
База данных ответит сообщением об успешном завершении:
Output
Query OK, 10 rows affected (0.002 sec)
Records: 10 Duplicates: 0 Warnings: 0Используйте оператор SELECT для проверки данных в только что созданной таблице:
SELECT * FROM club_members;
В результате отображается таблица, аналогичная той, что приведена в начале раздела:
Output
+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
| 4 | Samantha | Davis |
| 5 | Michael | Rodriguez |
| 6 | Tom | Thompson |
| 7 | Sara | Johnson |
| 8 | David | Jones |
| 9 | Jane | Smith |
| 10 | Bob | Johnson |
+-----------+------------+-----------+
10 rows in set (0.000 sec)Однако на этот раз в результатах появляется столбец Member_id, содержащий последовательность чисел от 1 до 10. Благодаря этому столбцу повторяющиеся строки для Джейн Смит и Боба Джонсона больше не распознаются, поскольку каждому имени присвоен уникальный идентификатор (Member_id).
Теперь давайте проверим, позволяет ли база данных добавить еще одного Тома Томпсона в список членов клуба:
INSERT INTO club_members (first_name, last_name) VALUES ('Tom', 'Thompson');
MySQL ответит сообщением об успешном завершении:
Output
Query OK, 1 row affected (0.009 sec)
Чтобы проверить, какой числовой идентификатор база данных присвоила новой записи, выполните запрос SELECT еще раз:
SELECT * FROM club_members;
В выходных данных есть еще одна строка:
Output
+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
| 4 | Samantha | Davis |
| 5 | Michael | Rodriguez |
| 6 | Tom | Thompson |
| 7 | Sara | Johnson |
| 8 | David | Jones |
| 9 | Jane | Smith |
| 10 | Bob | Johnson |
| 11 | Tom | Thompson |
+-----------+------------+-----------+
11 rows in set (0.000 sec)Новой строке автоматически присваивался номер 11 в столбце Member_id с помощью атрибута AUTO_INCREMENT базы данных.
Если в данных, с которыми вы работаете, нет естественных кандидатов на роль первичных ключей, и вы не хотите каждый раз при добавлении новых данных в базу данных придумывать новые идентификаторы, вы можете смело использовать последовательно генерируемые идентификаторы в качестве первичных ключей.
Результат
Следуя этому руководству, вы узнали, что такое первичные ключи и как создавать распространенные типы данных в MySQL для идентификации уникальных строк в таблицах базы данных. Вы создали естественные первичные ключи, создали первичные ключи, охватывающие несколько столбцов, и использовали автоинкрементные последовательные ключи там, где естественные ключи отсутствуют.
