Справочная целостность БД и внешний ключ

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

Итак, приступим к изучению целостности БД

Детали урока «Целостность бд, внешний ключ»

Тема:MySQL

Сложность:Средняя

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

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

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

Теперь о справочной целостности БД. Для того, чтобы понять указанный принцип, создадим БД городов мира. В этой БД мы будем хранить название города и название страны, в которой находится город. Легко понять, что если мы создадим 1 таблицу, то названия стран будут дублироваться, и при этом не раз. Если у нас есть 100 городов одной страны, то мы должны возле каждого города указать одно и то же название страны.

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

Таким образом, у нас появилось 2 таблицы — таблица стран и городов. При этом таблица стран — это т.н. справочник. Почему? Очень легко понять на примере. Итак, имеем таблицу стран с двумя странами: Украина и Россия. Идентификатор Украины будет 1, а России — 2. В таблице стран имеем 3 города: Киев, Москва, Львов. Для принадлежности каждого города к конкретной стране мы для них поставим идентификатор страны — у Киева и Львова — это 1, а у Москвы — 2. А расшифровка этих идентификаторов находится в таблице стран, т.е. в справочнике.

Теперь, если у страны изменится название, то мы меняем его только в справочнике, а таблицу городов не трогаем вообще, поскольку в ней нет названий стан (они в справочнике). Согласитесь, удобно. Фактически мы получили ситуацию, когда идентификатор страны из справочника (родительская таблица) используется в другой таблице (дочерней). Это как раз и есть упоминавшийся выше внешний ключ, который связывает поле дочерней таблицы с полем родителя.

Но пока что эта связь только у нас в уме. Для сервера ее нет. Соответственно, никаких ограничений такая связь не накладывает. Что нам мешает сейчас добавить в таблицу городов город с идентификатором страны 3? Т.е. это страна, не представленная в справочнике. Абсолютно ничего не мешает. Добавив указанную запись в таблицу городов мы как раз нарушаем целостность данных. К примеру, мы выводим на сайт список стран и по клику на страну выводим список городов этой страны. Наш «блудный» город без страны, таким образом, никогда выведен не будет. Получается, что это «лишняя», «болтающаяся» запись.

Чтобы избежать этого мы можем указать серверу связь внешнего ключа с родительским полем в справочнике. Для того, чтобы реализовать это, следует знать определенные правила, при которых такая связь вообще возможна. Давайте сформулируем их и затем перейдем к практике — созданию связи (соответственно, ограничений) по внешнему ключу. Итак:

1. Для использования ограничений внешнего ключа типы обоих таблиц должны быть InnoDB.

2. Типы родительского и дочернего полей должны быть абсолютно идентичными.

3. Связь по внешнему ключу опирается на индексы, поэтому и родительское, и дочернее поля должны содержать индексы.

4. Связь по внешнему ключу должна опираться на поле с ограничениями в родительской таблице, т.е. это поле должно содержать ограничения PRIMARY KEY или UNIQUE.

Вот и все. Теперь можем создать 2 таблицы с использованием ограничения по внешнему ключу.

Создаем таблицу городов:

CREATE TABLE country 
(
 country_id TINYINT UNSIGNED AUTO_INCREMENT NOT NULL, 
 country_name VARCHAR(50) NOT NULL, 
 PRIMARY KEY (country_id)
) ENGINE=InnoDB;

Создаем таблицу (справочник) стран:

CREATE TABLE city 
(
 city_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, 
 city_name VARCHAR(50) NOT NULL, 
 country_id TINYINT UNSIGNED NOT NULL, 
 PRIMARY KEY (city_id), 
 INDEX ixCity (country_id), 
 CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id)
) ENGINE=InnoDB;

Теперь давайте разберем указанные правила с учетом запросов. Поле с внешним ключом — это «city.country_id» (для того, чтобы не запутаться, перед названием поля мы указали через точку имя таблицы). Родительское поле (на которое ссылается внешний ключ) — это «country.country_id»

1. Для использования ограничений внешнего ключа типы обоих таблиц должны быть InnoDB.
Как видим, для обеих таблиц указан одинаковый тип — «ENGINE=InnoDB».

2. Типы родительского и дочернего полей должны быть абсолютно идентичными.
Это правило также выполняется. Тип дочернего поля «city.country_id» — «TINYINT UNSIGNED». Тип родителя «country.country_id» — также «TINYINT UNSIGNED».

3. Связь по внешнему ключу опирается на индексы, поэтому и родительское, и дочернее поля должны содержать индексы. Здесь также все ок. Дочернее поле мы индексируем, добавляя индекс в запросе — «INDEX ixCity (country_id)». Родитель индексируется автоматически. Дело в том, что для родителя мы имеем PRIMARY KEY, что как раз и предусматривает автоматический индекс для него.

4. Связь по внешнему ключу должна опираться на поле с ограничениями в родительской таблице, т.е. это поле должно содержать ограничения PRIMARY KEY или UNIQUE. И здесь все выполняется: «country.country_id» — «PRIMARY KEY (country_id)».

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

Но мы можем улучшить эту связь и сделать так, чтобы если мы что-то меняем в справочнике, то эти изменения касались также дочерней таблицы. Для этого после объявления ограничения по внешнему ключу мы можем записать выражения «ON DELETE CASCADE ON UPDATE CASCADE». По умолчанию значения этих выражений указаны «ON DELETE RESTRICT ON UPDATE RESTRICT», т.е. запрет. Таким образом, наш запрос станет таким:

CREATE TABLE city 
(
 city_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, 
 city_name VARCHAR(50) NOT NULL, 
 country_id TINYINT UNSIGNED NOT NULL, 
 PRIMARY KEY (city_id), 
 INDEX ixCity (country_id), 
 CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id) 
 ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Теперь, если мы удалим из справочника страну, то из дочерней таблицы без всяких дополнительных запросов будут удалены все города этой страны. Удобно, не правда ли? И не нарушается целостность данных.
Ну и напоследок давайте проследим, как можно организовать связь по внешнему ключу через популярное веб-приложение phpMyAdmin.

Итак, прежде всего, должны совпадать типы таблиц: