Как создать внешние и первичные ключи MySQL: инструкция

MySQL – это открытая реляционная система управления базами данных (СУБД), которая широко используется для хранения, управления и обработки структурированных данных. Она является одной из самых популярных и широко распространенных СУБД в мире, особенно в сфере разработки приложений и веб-сервисов.  

^{Изображение: wikipedia.org}

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

Первичный ключ и его предназначение

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

У таких ключей есть ряд отличительных свойств:

• Уникальность. Значения такого ключа не должны повторяться в таблице.
• Неизменяемость. В таблице можно выделить только один столбец для такого ключа. Если пользователю требуется два и более таких полей, то он должен использовать составные ключи MySQL. В рамках данной статьи мы не будет заострять на них внимание.
• Ненулевое значение (Not Null). Значение такого ключа не может быть нулевым (NULL).

Ниже приведем пример таблицы users из некой БД, которая содержит рассматриваемый ключ:

В этом примере, поле user_id – это и есть первичный ключ, который уникально идентифицирует каждую запись. Так, для пользователя с никнеймом example_user1 был создан user_id, равный 1, для пользователя example_user2 был создан user_id, равный 2, и так далее.

Способы создания первичного ключа

Чтобы создать первичный ключ в MySQL на этапе формирования таблицы, пользователь должен добавить соответствующую строку в команду:

CREATE TABLE table_name (
…
field_name data_type PRIMARY KEY,
…
);

Здесь на создание поля с первичным ключом указывает ключевое слово PRIMARY KEY.

Команда ALTER TABLE используется для изменения структуры ранее созданных таблиц и позволяет добавлять, изменять и удалять столбцы, а также устанавливать различные ограничения и индексы. Она также используется и для добавления рассматриваемого ключа. Синтаксис этого процесса представлен ниже:

ALTER TABLE table_name
ADD PRIMARY KEY (field_name);

Чтобы удалить этот ключ в MySQL, пользователь также может воспользоваться рассмотренной выше командой:

ALTER TABLE table_name
DROP PRIMARY KEY;

Внешний ключ и его предназначение

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

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

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

Способы создания внешнего ключа

Чтобы создать внешний ключ в MySQL при формировании таблицы, пользователю необходимо добавить следующую запись в команду:

CREATE TABLE child_table_name (
...
child_field_name data_type,
FOREIGN KEY (child_field_name) REFERENCES parent_table_name(parent_field_name),
...
);

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

ALTER TABLE child_table_name
ADD FOREIGN KEY (child_field_name) REFERENCES parent_table_name(parent_field_name);

Чтобы удалить внешний ключ из таблицы, необходимо воспользоваться следующей командой:

ALTER TABLE table_name
DROP FOREIGN KEY field_name;

При создании внешнего ключа, к нему можно добавить параметры, которые будут задавать действия для строк дочерней таблицы при обновлении (ON UPDATE) или удалении (ON DELETE) строки из главной таблицы. Добавление параметров происходит после оператора REFERENCES. Список возможных параметров представлен ниже:

• Каскадное удаление (CASCADE)

Если вы удаляете (обновляете) строку из родительской таблицы, то MySQL автоматически удалит (обновит) связанные строки из дочерней таблицы.

• Установка NULL (SET NULL)

Если вы удаляете (обновляете) строку из родительской таблицы, то MySQL автоматически установит для внешнего ключа в дочерней таблице значение NULL.

• Ограничение (RESTRICT)

Если вы удаляете (обновляете) строку из родительской таблицы, то MySQL автоматически отменит это действие (выдаст ошибку), если есть связанные строки в дочерней таблице.

• Без действия (NO ACTION) – аналогична RESTRICT

На этом знакомство с первичным и внешним ключом закончено. Теперь перейдем к практической части статьи, где продемонстрируем применение двух этих ключей.

Пример создания ключей в БД

В данной главе, в качестве примера, создадим БД на сервере Timeweb Cloud. 

1. В первую очередь запускаем MySQL на сервере:

sudo mysql

2. Далее создадим новую базу данных и сразу перейдем к работе с ней:

create database TestDB;
use TestDB;

3. В качестве примера создадим две базы данных. Первая будет хранить категории сервисов Timeweb Cloud, а вторая – список сервисов разных категорий. 

Для создания первой таблицы, вводим в консоль:

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(30) NOT NULL
);

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

4. Теперь создадим вторую таблицу:

CREATE TABLE Services (
    ServiceID INT PRIMARY KEY,
    ServiceName VARCHAR(30) NOT NULL,
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

В создаваемой таблице:

• ServiceID – первичный ключ, идентифицирующий сервис;
• ServiceName – название сервиса;
• CategoryID – внешний ключ, связывающий сервис с конкретной категорией. Этот внешний ключ ссылается на столбец CategoryID в таблице «Категории».

5. Далее заполним обе таблицы тестовыми данным.

Всего у компании выделено три категории сервисов, поэтому для первой таблицы данные будут следующими:

INSERT INTO Categories (CategoryID, CategoryName)
VALUES
    (1, 'Calculations'),
    (2, 'Services'),
    (3, 'Administration');

Для второй таблицы возьмем несколько сервисов из разных категорий:

INSERT INTO Services (ServiceID, ServiceName, CategoryID)
VALUES
    (101, 'Cloud servers', 1),
    (102, 'Dedicated servers', 1),
    (103, 'Cloud databases', 2),
    (104, 'Load Balancer', 2),
    (105, 'Microsoft Licenses', 3);

Теперь созданные таблицы имеют следующий вид:

6. И, наконец, проверим работу созданного внешнего ключа. Для этого составим запрос, используя оператор LEFT JOIN, для вывода общей информации о всех сервисах и их категориях в компании:

SELECT Services.ServiceID, Services.ServiceName, Categories.CategoryName FROM Services 
LEFT JOIN Categories ON Services.CategoryID = Categories.CategoryID;

Как итог, получим следующую таблицу:

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