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

Ссы́лочная це́лостность (англ. referential integrity) — необходимое качество реляционной базы данных, заключающееся в отсутствии в любом её отношении внешних ключей, ссылающихся на несуществующие кортежи.

Определение

Связи между данными, хранимыми в разных отношениях, в реляционной БД устанавливаются с помощью использования внешних ключей — для установления связи между кортежом из отношения A с определённым кортежом отношения B в предусмотренные для этого атрибуты кортежа отношения A записывается значение первичного ключа (а в общем случае значение потенциального ключа) целевого кортежа отношения B. Таким образом, всегда имеется возможность выполнить две операции:

• определить, с каким кортежом в отношении B связан определённый кортеж отношения A;
• найти все кортежи отношения A, имеющие связи с определённым кортежом отношения B.

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

Дана пара отношений A и B, связанных внешним ключом. Первичный ключ отношения B — атрибут B.key. Внешний ключ отношения A, ссылающийся на B — атрибут A.b. Ссылочная целостность для пары отношений A и B имеет место тогда, когда выполняется условие: для каждого кортежа отношения A существует соответствующий кортеж отношения B, то есть кортеж, у которого (B.key = A.b).

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

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

Пример

Так, в примере реляционная БД, состоящая из таблиц Address и Street, обеспечивает хранение адресов. При этом основная таблица, — Address, — содержит непосредственно номер дома и квартиры, а вместо имени улицы в поле Street имеет внешний ключ, ссылающийся на таблицу Street — справочник улиц. Очевидно, что полноценный адрес должен быть представлен двумя связанными записями в обеих названных таблицах, что технически выражается в условии: для любой записи таблицы Address в таблице Street должна существовать соответствующая запись, то есть запись со (Street.Key = Address.Street). Чтобы получить список полных адресов из таблиц такой структуры, когда в них соблюдается ссылочная целостность, достаточно применить к данным таблицам SQL-запрос:

 select * 
 from Address, Street
 where
   Address.Street = Street.Key

В данном примере, однако, ссылочная целостность нарушена. Две записи таблицы Address (Key = 887 и Key = 994) имеют в поле Street так называемые «висящие» ссылки — значения, которым не соответствуют записи в таблице Street (эти ссылки показаны красным цветом). Из-за этого результат вышеприведённого запроса не будет содержать этих двух записей — для них условие запроса не выполнится. И ещё одна запись не будет выбрана вышеприведённым запросом — запись таблицы Address с (Key = 85). Это вариант намеренного (и, в некоторых случаях, легального) нарушения ссылочной целостности — в поле внешнего ключа записан NULL (показано голубым цветом). Чтобы получить список всех адресов, даже тех, у которых не указана улица, необходимо использовать открытое соединение, в одном из вариантов синтаксиса записываемое так:

 select * 
 from Address left outer join Street on (Address.Street = Street.Key)

Если же требуется получить список, не включающий записи с «висящими» ссылками, то придётся усложнить запрос:

 select *  
 from Address left outer join Street on ((Address.Street = Street.Key) or (Address.Street is null))

Поддержание ссылочной целостности в БД

Причины нарушений

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

• Некорректная работа прикладного программного обеспечения. Понятно, что при ошибке в программе, выполняющей модификацию базы данных, база может быть модифицирована недопустимым образом, в результате чего образуются «висящие» ссылки. Программа может совершать ошибки следующих видов:
  • Неполная запись объектов. Данные объекта размещаются в записях нескольких таблиц, а программа не записывает какую-то из них.
  • Некорректная правка ссылки. Значение внешнего ключа изменяется на такое, которому не соответствует ни одна запись в связанной таблице.
  • Правка первичного ключа без каскадного обновления. В таблице, на которую есть ссылки, правится первичный ключ, но при этом внешние ключи в связанных с ней таблицах остаются без изменения.
  • Удаление записи без каскадного обновления. Из таблицы удаляется запись, на которую имеются ссылки по внешним ключам других таблиц, при этом в связанных записях внешние ключи не меняются. В результате все ссылающиеся на неё записи других таблиц становятся некорректными.
• Сбои в работе системного программного обеспечения и оборудования. Даже когда прикладное программное обеспечение работает совершенно правильно, возможно нарушение ссылочной целостности. Например, если при добавлении объекта в базу нужно добавить несколько связанных записей в несколько таблиц, очевидно, что ссылочная целостность будет нарушена в процессе добавления данных (когда часть связанных записей уже добавлена, а часть — ещё нет), и восстановится только после завершения операции. Если во время выполнения операции она будет прервана (из-за переполнения диска, сбоя питания, или по каким-то другим причинам), часть записей будет добавлена в БД, часть — нет. Часть добавленных записей останется с некорректными ссылками.

Пустые внешние ключи

Возможна ситуация, когда внешний ключ вместо ссылки на существующую запись в таблице БД содержит «отсутствующее значение» NULL. Такое положение можно трактовать как отсутствие какой-то части объекта. Хотя с точки зрения чистой теории это недопустимо, на практике иногда бывает удобно разрешить использование пустых внешних ключей. Чтобы корректно работать с группами связанных таблиц, допускающих пустые внешние ключи, используется специфическая операция языка SQL — открытое соединение (другое название — «внешнее соединение», англ. outer join).

Транзакции

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

Ссылочная целостность на триггерах

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

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

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

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

Ссылочная целостность на внешних ключах

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

• При операции добавления или редактирования записи автоматически проверяется, ссылаются ли внешние ключи в этой записи на существующие записи в заявленных при описании связанных таблицах. Если выясняется, что операция приведёт к появлению некорректных ссылок, она не выполняется — система возвращает ошибку.
• При операции редактирования записи проверяется, не изменяется ли её первичный ключ и нет ли на неё ссылок. Если первичный ключ изменяется, и при этом на данную запись имеются ссылки, то операция редактирования завершается с ошибкой.
• При операции удаления записи проверяется, нет ли на неё ссылок. Если ссылки имеются, то возможно три варианта дальнейших действий (фактически выполняемый зависит от СУБД и от выбора программиста, который он должен сделать при описании связи):
  • Запрет — удаление блокируется и возвращается ошибка.
  • Каскадное удаление — в одной транзакции производится удаление данной записи и всех записей, ссылающихся на данную. Если на удаляемые записи также есть ссылки и настройки также требуют удаления, то каскадное удаление продолжается дальше. Таким образом, после удаления данной записи в базе не остаётся ни одной записи, прямо или косвенно ссылающейся на неё. Если хотя бы одну из ссылающихся записей удалить не получается (либо для неё настроен запрет, либо происходит какая-либо ещё ошибка), то все удаления запрещаются.
  • Обнуление внешних ключей — во все внешние ключи записей, ссылающихся на данную, записывается псевдозначение NULL (SQL). Если хотя бы для одной из ссылающихся записей это невозможно (например, если поле внешнего ключа описано так, что его нельзя обнулять), то удаление запрещается.