Microsoft Access: Советы для серьезных пользователей

Автор Allen Browne, июль 2006.  последнее обновление: апрель 2010 г.

Перевел с английского Александр Артамонов, ноябрь 2011 г.

Оригинал http://allenbrowne.com/ser-64.html

Отношение с каскадной установкой 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-а:

  1. Связанные записи не теряются!
  2. Поддерживается целостность. (Нет записей с невалидным внешним ключом.)
  3. Значение 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".

  1. Откройте Northwind.mdb в Аксессе 2000 или более поздней версии.
  2. Откройте окно "Схема данных".
     В Аксессе 2000 - 2003, выберите Схема данных из меню Сервис.
     В Аксессе 2007 и более поздних версиях, нажмите на Связи в закладке ленты Сервис
  3. Удалите существующую связь между Products и Categories: нажмите правой кнопкой мыши по линии, соединяющей их и выберите "Удалить". Закройте окно "Схема данных".
  4. Создайте новый модуль.
    В Аксессе 2000 - 2003, выберите иконку/закладку?? "Модули" в окне базы данных и нажмите.
    В Аксессе 2007 и более поздних версиях, выберите "Модули" (крайняя иконка справа) на ленте "Создать".
  5. Вставьте код ниже в новый модуль.
  6. Запустите код: откройте окно Immediate (Ctrl+G) и введите:
        Call MakeRel()
    Ответом будет значение для аттрибута связи "каскадный Null":
        8192

Вот код:

'Определить значение бита для аттрибута связи.
Public Const dbRelationCascadeNull As Long = &H2000
Public Function MakeRel()
    'Цель: Создать связь "каскадный Null" с помощью DAO.
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field

    Set db = CurrentDb()
    'Аргументы для CreateRelation(): любое уникальное имя, главная таблица, дочерняя таблица, аттрибуты.
    Set rel = db.CreateRelation("CategoriesProducts", "Categories", "Products", dbRelationCascadeNull)
    Set fld = rel.CreateField("CategoryID")  'Поле из главной таблицы.
    fld.ForeignName = "CategoryID"           'Соответствующее поле из дочерней таблицы.
    rel.Fields.Append fld                    'Добавьте поле к коллекции полей связи.
    db.Relations.Append rel                  'Добавьте связь к базе данных.


    'Отчет и уборка.
    Debug.Print rel.Attributes
    Set db = Nothing
End Function

Чтобы протестировать код, откройте таблицу Categories и добавьте новую категорию, например, "Goofy Food", и закройте таблицу. Откройте таблицу Products, измените категорию пары продуктов на новую категорию и закройте таблицу. Затем снова откройте таблицу Categories и удалите категорию Goofy Food. Вы увидите следующее диалоговое окно:

 

Выберите "Да". Откройте таблицу Products, и вы увидите, что продукты, отнесенные раньше к категории Goofy Food теперь не имеют категории. Удаление категории вызвало каскадную установку в Null.

(Обратите внимание, что у Аксесса нет диалогового окна для каскадного Null-а, так что он использует сообщение для каскадного удаления.)

Поддержание связей типа "каскадный Null"

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

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

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

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

Наименование поля Тип данных Описание
* * * WARNING * * * Text Только для информации: данных нет.
Cascade Text  
to Text  
Null Text  
Relations Text  
Exist Text  
On Text  
Products Text  
And Text  
Categories Text  
Id Number Первичный ключ

Теперь откройте окно Схема данных (меню Сервис) и добавьте таблицу. Перетащите поле CategoryID из таблицы Categories на поле Id этой новой таблицы и создайте связь.

 

Пример из реального мира

Связь "Каскадный Null" полезна и кроме простого вышепоказанного примера для "категории". На самом деле, такая связь достойна рассмотрения в любом отношении, где внешний ключ необязателен.

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

Используя отношения "каскадный Null" между инвойсом и оригинальной записью реестра продаж означает,что если вы удаляете инвойс (или целый пакет инвойсов), Аксесс автоматически обновляет все записи продаж обратно к Null. Следующий раз, когда идет процесс пакетной обработки, ваш код определяет, что записи продаж не являются частью счета и выбирает эти записи автоматически.

Вероятно у вас есть каскадное удаление между таблицей Batch и таблицей Invoice. Таким образом, вы теперь можете удалить запись одного пакета инвойсов: связанные инвойсы удаляются, а оригинальные записи в реестре продаж каскадно устанавливаются в Null. Никакого кода. Нет шанса сделать ошибку: все поддерживается JET-ом.

Заключение

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

 

Примечание переводчика:
 Создать связь обсуждаемого типа можно также выполнением команды CREATE/ALTER TABLE.... FOREIGN KEY... ON DELETE SET NULL.. через ADO.
Конструктор сайтов - uCoz