Ограничение ссылочной целостности в родительской таблице

Предположим, что, но таблицам, ассоциированным с сущностями «Заказы» и «Товары заказа», имеются некоторые ранее сформированные сведения, которые позволяют увидеть возможные действия и возможные проблемы, возникающие при добавлении, удалении и изменении данных в родительской таблице «Заказы» (рис. 5.43).

Рис. 5.43. Исходные данные для проверки ограничений ссылочной целостности.

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

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

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

Предположим, что изменили значение первичного ключа заказа с «2» на «4». Учитывая, что в дочерней таблице по заказу со значением ключа «2» имеется позиция товара, то бесконтрольное изменение приведет к потере этой позиции товара в заказа, что недопустимо.

Рис. 5.44. Изменение значения первичного ключа родительской таблицы Разрешение этой ситуации может выполняться различными вариантами:

первым, наиболее корректным, является указание действия «Cascade», поскольку тогда значение внешнего ключа в дочерней таблице по связанным записям автоматически будет изменено на правильное значение и нарушения целостности не будет;

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

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

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

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

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

Таким образом, учитывая, что товары не могут храниться в таблице базы данных «Товары заказа» без указания заказа, за которым они закреплены, допустимыми вариантами могут быть «Restrict» и «Cascade». При рассмотрении возможности удаления заказа вместе с заказом, что, но предметной области допустимо, вариант действия «Cascade» является наиболее корректным и верным.