Microsoft Access: Советы для серьезных пользователейАвтор Allen Browne, июль 2006. последнее обновление: апрель 2010 г. Перевел с английского Александр Артамонов, ноябрь 2011 г. Оригинал http://allenbrowne.com/ser-64.html Содержание:Ссылочная целостность и Null-ы Создание связи типа "каскадный Null" Отношение с каскадной установкой NullВкратце: Описание малоизвестной возможности Аксесса, позволяющей автоматически присваивать Null связанным записям вместо удаления при удалении записей в главной таблице. ВведениеПриходилось ли вам создавать таблицу с внешним ключом, являющимся null-ом до момента совершения пакетной операции? Например, некоммерческая организация рассылает благодарственные письма дарителям в конце отчетного периода. Таблица пожертвований (Donation) содержит поле LetterID, которое хранит Null, пока не будет выполнена пакетная операция по созданию письма для каждого дарителя и присвоению этого LetterID каждой записи в таблице Donation, которые подтверждаются письмом. Чтобы дать пользователю возможность отменить рассылку, вам приходится писать код для выполнения запроса на обновление для таблицы Donation, чтобы вернуть Null для всех LetterID писем из этой рассылки, удалить письма из таблицы Letters, и удалить код рассылки BatchID из таблицы рассылок. Да, да, вот именно так до сих пор вы и писали код для отмены партии! Теперь существует способ заставить JET (движок баз данных Аксесс) автоматически возвращать LetterID обратно к Null, когда письма удаляются, на уровне движка, без единой строчки кода. Каскадный Null был введен шесть лет назад, но остается ниже радара для большинства разработчиков. Эта статья описывает, как создать такой вид каскадного отношения, с демонстрации на простом примере для Northwind и образец базы данных (в сжатом виде 13 Кб) иллюстрирующем подход как для DAO, так и для ADOX. Но сперва краткий обзор Null-ов во внешних ключах. Ссылочная целостность и Null-ыКогда вы создаете связь в Аксессе, вы почти всегда ставите флажок для ссылочной целостности (СЦ). Этот маленький чекбокс блокирует ввод невалидных записей в связанной таблице и открывает дверь для каскадных обновлений и удалений. Чего этот чекбокс не делает, так это не запрещает Null-ы во внешнем ключе. В большинстве случаев вы должны предотвращать такую возможность путем установки свойства Обязательное в поле внешнего ключа этой таблицы. Но есть случаи, когда есть полный смысл разрешать Null во внешнем ключе. Массовые изменения, такие как операция над партией писем в примере выше, является нередким случаем. Даже для такой простой вещи, как категоризация объектов, вы вероятно захотите разрешить объекты без категорий, так чтобы внешний ключ CategoryID мог быть Null-ом. Что такое "каскадный Null"?Мы упомянули три варианта по которым движок базы данных может реализовать ссылочную целостность:
Существует четвертый способ, которым база данных может поддерживать СЦ: когда запись удаляется из главной таблицы, движок базы данных устанавливает поле внешнего ключа всех соответствующих записей на Null. Преимущества каскадного Null-а:
Представьте, что пользователь создал идиотскую/тестовую???goofy категорию в Борее, и присвоил ее нескольким продуктам. Вам нужно удалить категорию, но не теряя продукты. При связи такого вида вы можете просто удалить категорию и все соответствующие продукты. Никакого кода. Никаких запросов на изменение. Никакого тестирования: движок позаботится об этом за вас. Это и есть "каскадный Null": при удалении первичной записи, внешний ключ соответствующих записей автоматически устанавливается на Null. Создание связи типа "каскадный Null"Как такая великолепная возможность осталась неизвестной для большинства разработчиков? Майкрософт дал нам эту возможность в Аксессе 2000, но они так и не обновили интерфейс. В окне редактирования связей нет чекбокса для каскадного Null-а. Связь такого вида можно создать только программно. As the example below demonstrates, the code is very simple. These steps work with Northwind to replace the relation between Products and Categories with a cascade-to-null. Как демонстрирует пример ниже, код очень прост. Эти шаги написаны для Борея, чтобы заменить связь между Products и Categories на "каскадный Null".
Вот код: 'Определить значение бита для аттрибута связи. Чтобы протестировать код, откройте таблицу Categories и добавьте новую категорию, например, "Goofy Food", и закройте таблицу. Откройте таблицу Products, измените категорию пары продуктов на новую категорию и закройте таблицу. Затем снова откройте таблицу Categories и удалите категорию Goofy Food. Вы увидите следующее диалоговое окно:
Выберите "Да". Откройте таблицу Products, и вы увидите, что продукты, отнесенные раньше к категории Goofy Food теперь не имеют категории. Удаление категории вызвало каскадную установку в Null. (Обратите внимание, что у Аксесса нет диалогового окна для каскадного Null-а, так что он использует сообщение для каскадного удаления.) Поддержание связей типа "каскадный Null"Так как целостность ваших данных поддерживается на уровне движка, такой тип связи требует меньше запросов на обновление. Это в свою очередь означает, что нужно писать меньше кода, так как движок позаботится об этом за вас. Но что, если кому-нибудь еще понадобиться переделать базу данных на каком-то этапе? Поскольку интерфейс не покажет им, что действуют связи "каскадный Null", они могут пересоздать таблицы, не имея понятия, что ваше приложение полагается на такой тип каскадных изменений. Вам нужен способ задокументировать это. В идеале это должно быть видно в окне схемы данных. Создайте таблицу исключительно для документирования. Таблица не будет содержать записей. Чтобы показать ее в окне схемы данных, создайте связь с другими таблицами, чтобы она не только была сохранена в окне схемы данных в текущий момент, но и появилась бы, когда нажата кнопка Показать все связи. Имена полей могут быть любые, но так как целью является привлечь внимание, то можно составить предложение, используя необычные зарезервированные слова:
Теперь откройте окно Схема данных (меню Сервис) и добавьте таблицу. Перетащите поле CategoryID из таблицы Categories на поле Id этой новой таблицы и создайте связь.
Пример из реального мираСвязь "Каскадный Null" полезна и кроме простого вышепоказанного примера для "категории". На самом деле, такая связь достойна рассмотрения в любом отношении, где внешний ключ необязателен. Например, у вас могут быть данные из реестра продаж, которые нужно собрать в инвойс для каждого клиента в конце месяца. Так как данные по продажам станут строками инвойса, у них есть внешний ключ InvoiceID, который содержит Null, пока инвойсы не будут сгенерированы. Новым инвойсам будет присвоен номер пакета, так что пользователь может отменить целый пакет, если что-то пойдет не так, исправить данные и запустить процесс заново. Используя отношения "каскадный Null" между инвойсом и оригинальной записью реестра продаж означает,что если вы удаляете инвойс (или целый пакет инвойсов), Аксесс автоматически обновляет все записи продаж обратно к Null. Следующий раз, когда идет процесс пакетной обработки, ваш код определяет, что записи продаж не являются частью счета и выбирает эти записи автоматически. Вероятно у вас есть каскадное удаление между таблицей Batch и таблицей Invoice. Таким образом, вы теперь можете удалить запись одного пакета инвойсов: связанные инвойсы удаляются, а оригинальные записи в реестре продаж каскадно устанавливаются в Null. Никакого кода. Нет шанса сделать ошибку: все поддерживается JET-ом. ЗаключениеВсе, что оперирует на уровне движка базы данных, воплощается просто, надежно и не требует поддержки разработчика. Как только вы начнете использовать этот вид правила удаления, он станет бесценным.
Примечание переводчика: |