Как использовать первичные ключи в SQL

Автор выбрал программу Write for DOnations.

Введение

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

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

Предпосылки

Чтобы следовать этому руководству, вам понадобится компьютер с системой управления реляционными базами данных (RDBMS) на основе SQL. Инструкции и примеры в этом руководстве были проверены с использованием следующей среды:

• Сервер под управлением Ubuntu 20.04, пользователь без полномочий root с правами администратора и брандмауэр, настроенный с помощью UFW, как описано в нашем руководстве по первоначальной настройке сервера для Ubuntu 20.04.
• MySQL установлен и защищен на сервере, как описано в шаге 3.
• Основные знания о выполнении запросов SELECT для извлечения данных из базы данных, как описано в нашем руководстве «Выбор строк из таблиц в SQL».

Примечание. Обратите внимание, что многие СУБД используют собственные уникальные реализации SQL. Хотя команды, описанные в этом руководстве, будут работать с большинством СУБД, а первичные ключи являются частью стандарта SQL, некоторые функции зависят от базы данных, поэтому точный синтаксис или выходные данные могут отличаться, если вы тестируете их в системе, отличной от MySQL.

Вам также понадобится пустая база данных, в которой вы сможете создавать таблицы, демонстрирующие использование первичных ключей. Мы рекомендуем вам ознакомиться со следующим разделом «Подключение к MySQL» и «Настройка образца базы данных», чтобы получить подробную информацию о подключении к серверу MySQL и создании тестовой базы данных, используемой в примерах в этом руководстве.

Подключение к MySQL и настройка образца базы данных

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

Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локального компьютера:

ssh sammy@your_server_ip

Затем откройте приглашение сервера MySQL, заменив sammy именем вашей учетной записи пользователя 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.
• brand: этот столбец содержит марку автомобиля, выраженную с использованием типа данных varchar с максимальным числом символов 50.
• model: в этом столбце содержится модель автомобиля, выраженная с использованием типа данных varchar с максимальным числом символов 50.
• color: этот столбец содержит цвет, выраженный с использованием типа данных varchar с максимальным количеством символов 20.
• year: в этом столбце указан год выпуска автомобиля, выраженный с использованием типа данных int для хранения числовых данных.

Чтобы создать таблицу cars, выполните следующую инструкцию 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. При работе с первичными ключами на основе отдельных столбцов вы можете использовать упрощенный синтаксис для создания ключа, написав PRIMARY KEY в определении столбца.

Если выводятся следующие выходные данные, таблица создана:

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 символов. Он не использует числовой тип данных int, поскольку некоторые номера улиц могут содержать буквы (например, 200B).
• house_owner: в этом столбце содержится имя владельца дома, представленное типом данных varchar, ограниченным 50 символами.
• year: в этом столбце указан год постройки дома, представленный типом данных int для хранения числовых значений.

На этот раз первичный ключ будет использовать оба столбца 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 и . улица_номер. Этот синтаксис создает первичный ключ в таблице 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)

Однако попробуйте добавить еще один дом на 5th Avenue, 100, используя инструкцию 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'

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

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

Создание последовательного первичного ключа

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

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

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 для идентификации уникальных строк в таблицах базы данных. Вы создали естественные первичные ключи, создали первичные ключи, охватывающие несколько столбцов, и использовали автоматически увеличивающиеся последовательные ключи там, где естественных ключей нет.

Вы можете использовать первичные ключи для дальнейшего формирования структуры базы данных, гарантируя, что строки данных будут однозначно идентифицируемы. В этом руководстве были рассмотрены только основы использования первичных ключей. Чтобы узнать больше об этом, обратитесь к How To Use Constraints in SQL.

Если вы хотите узнать больше о различных концепциях языка SQL и работе с ним, мы рекомендуем вам ознакомиться с другими руководствами из серии «Как использовать SQL».