Ограничения ссылочной целостности

При реализации базы данных разработчики регулярно сталкиваются с обеспечением корректности представляемых данных в таблицах, что заключается в исключении следующих проблем:

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

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

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

Такими операциями, которые выполняются при обеспечении ссылочной целостности являются:

• • Restrict — действие запрещает операции, если существуют связанные записи;
• • Cascade — действие определяет последовательное выполнение операции над связанными данными в дочерней таблице;
• • Set NULL — действие устанавливает пустое значение внешнему ключу при невозможности связать по нему запись с родительской таблицей;
• • Set Default — действие, аналогичное предыдущему варианту, но устанавливается не пустое значение, а определенное значение по умолчанию;
• • No action — действие не предполагает стандартных действий ограничения ссылочной целостности, но разрешает использование триггерных операций;
• • None — запрещаются все возможные действия, включая триггерные.

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

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

При взаимодействии таблиц, ассоциированных с сущностями «Заказы» и «Клиент», если добавляется новый заказ, то условиями использования внешнего ключа, поскольку для него установлено свойство невозможности хранения пустого значения, предполагается его обязательное заполнение данными. К тому же условие наличия внешнего ключа в базах данных определяет автоматическую ненастраиваемую операцию контроля значений с обеспечением однозначного соответствия значения внешнего ключа с существующими значениями первичного ключа родительской таблицы. Эта особенность и отсутствие необходимости выполнения каких-либо операций, помимо контроля значения внешнего ключа, приводят к решению установить вариант ограничения ссылочной целостности «None» .

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

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

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

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

Рис. 5.42. Алгоритм реализации операций ссылочной целостности.

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