Интернет и базы данных. Часть 04. Ключи и ссылочная целостность

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

Первичный ключ

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

Уникальный ключ

Уникальный ключ - это столбец или группа столбцов, значения (комбинация значений для группы столбцов) которых не могут повторяться. Отличия уникального ключа от первичного - в том, что:

• уникальных ключей для одной таблицы может быть несколько (вопросик на засыпку для тех, кто прочитал статью про нормализацию: правила какой нормальной формы при этом будут нарушены? ;)
• уникальные ключи могут иметь значения NULL, при этом если имеется несколько строк со значениями уникального ключа NULL, такие строки согласно стандарту SQL 92 считаются различными (уникальными).

Внешний ключ

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

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

И самое главное. Все значения внешнего ключа должны совпадать с каким-либо из значений родительского ключа. (Заметим в скобках насчет совпадения / несовпадения: нюансы возникают, когда в значениях столбцов вторичного ключа встречается NULL. Давайте пока в эти нюансы вдаваться не будем). Появление значений внешнего ключа, для которых нет соответствующих значений родительского ключа, недопустимо. Вот тут-то мы плавно переходим к понятию ссылочной целостности.

Ссылочная целостность

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

Более интересные моменты возникают, когда мы удаляем или изменяем строки родительской таблицы. Как при этом не допустить появления \"болтающихся в воздухе\" строк дочерней таблицы? Для этого существуют правила ссылочной целостности ON UPDATE и ON DELETE, которые, по стандарту SQL 92, могут содержать следующие инструкции:

• CASCADE - обеспечивает автоматическое выполнение в дочерней таблице тех же изменений, которые были сделаны в родительском ключе. Если родительский ключ был изменен - ON UPDATE CASCADE обеспечит точно такие же изменения внешнего ключа в дочерней таблице. Если строка родительской таблицы была удалена, ON DELETE CASCADE обеспечит удаление всех соответствующих строк дочерней таблицы.
• SET NULL - при удалении строки родительской таблицы ON DELETE SET NULL установит значение NULL во всех столбцах вторичного ключа в соответствующих строках дочерней таблицы. При изменении родительского ключа ON UPDATE SET NULL установит значение NULL в соответствующих столбцах соответствующих строк (о как:) дочерней таблицы.
• SET DEFAULT - работает аналогично SET NULL, только записывает в соответствующие ячейки не NULL, а значения, установленные по умолчанию.
• NO ACTION (установлено по умолчанию) - при изменении родительского ключа никаких действий с внешним ключом в дочерней таблице не производится. Но если изменение значений родительского ключа приводит к нарушению ссылочной целосности (т.е. к появлению "висящих в воздухе" строк дочерней таблицы), то СУБД не даст произвести такие изменения родительской таблицы.

Ну а сейчас - от общего к частному.

Ключи и ссылочная целостность в MySQL и Oracle

Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:

• NO ACTION (устанавливается по умолчанию) в более жестком, чем по стандарту SQL 92, варианте: запрещается изменение и удаление строк родительской таблицы, для которых имеются связанные строки в дочерних таблицах.
• ON DELETE CASCADE.

Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.

MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.

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