Что такое каскадное обновление связанных полей

Каскадное обновление и удаление связанных записей

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

В режиме каскадного удаления связанных записей при удалении записи из глав-

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

В режиме каскадного обновления связанных полей при изменении значения ключевого поля в записи главной таблицы Access автоматически изменит значения

в соответствующем поле в подчиненных записях.

Установить в окне Изменение связей (Edit Relationships) (см. рис. 3.49) флаж-

ки каскадное обновление связанных полей (Cascade Update Related Fields) и каскадное удаление связанных записей (Cascade Delete Related Records) можно только после задания параметра обеспечения целостности данных.

Рис. 3.52. Схема данных БД Поставка товаров

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

канонической модели данных, полученной при проектировании базы данных в гла-

ве 2 (см. рис. 2.18).

На рис. 3.52 в созданной схеме данных БД Поставка товаров все связи отмечены символами 1 и . Это свидетельствует о том, что одно-многозначные связи установлены правильно (по простому или составному ключу) и для них задан параметр обеспечения целостности данных.

Задание 3.7. Проверка поддержания целостности в базе данных

Если в схеме данных определена связь таблиц, и для нее установлены параметры обеспечения целостности, при вводе и корректировке данных во взаимосвязанных таблицах пользователь не сможет ввести записи, нарушающие требования связной целостности, рассмотренные в предыдущем разделе. Проверьте, как обеспечивается поддержание целостности при внесении изменений в таблицы ПОКУПАТЕЛЬ — ДОГОВОР, связанные одно-многозначными отношениями.

Проверка автоматического поддержания целостности при изменении зна-

чений ключей связи в таблицах. Откройте главную таблицу связи ПОКУПАТЕЛЬ в режиме таблицы. Измените значение ключевого поля КОД_ПОК (код покупателя) в одной из записей. Убедитесь, что во всех записях подчиненной таблицы ДОГОВОР для договоров, заключенных этим покупателем, автоматически также изменится значение поля КОД_ПОК. Изменение происходит, т. к. был установлен флажок каскадное обновление связанных полей (Cascade Update Related Fields) (см. рис. 3.49). Причем это изменение осуществляется мгновенно, как только изменяемая запись перестает быть текущей.

Проверка при добавлении записей в подчиненную таблицу. Убедитесь, что невозможно включить новую запись в подчиненную таблицу ДОГОВОР со значением ключа связи КОД_ПОК, не представленным в таблице ПОКУПАТЕЛЬ. Измените значение ключа связи КОД_ПОК в подчиненной таблице ДОГОВОР на значение, не существующее в записях таблицы ПОКУПАТЕЛЬ, и убедитесь, что такое изменение запрещено, т. к. при поддержании целостности не может существовать запись подчиненной таблицы с ключом связи, которого нет в главной таблице.

Проверка при удалении записи в главной таблице. Убедитесь, что вместе с удалением записи в главной таблице ПОКУПАТЕЛЬ удаляются все подчиненные записи в таблице ДОГОВОР, т. к. был установлен флажок каскадное удаление связанных записей (Cascade Delete Related Records).

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

Большая Энциклопедия Нефти и Газа

Каскадное обновление означает, что изменение значения связанного поля в главной таблице ( например, кода клиента) автоматически будет отражено в связанных записях подчиненной таблицы. Иными словами, если изменился код клиента в словаре клиентов, то он будет заменен и во всех заказах данного клиента. [3]

Чем отличается каскадное обновление от каскадного удаления. [5]

Механизм поддержки целостности позволяет организовать каскадное обновление полей и каскадное удаление записей БД при изменениях в главной из связанных таблиц. [6]

Полезно также установить флажок Обеспечение целостности данных и два флажка, которые отвечают за каскадное обновление и удаление данных. [7]

Если между таблицами определено отношение один ко многим и в диалоговом окне Изменение связей установлен флажок опции каскадное обновление связанных полей , при любом изменении данных первичного ключа в главной таблице автоматически будут обновлены иучы — кгпуютис значения в поле внешнего ключа подчиненной таблицы. Целостность данных таким образом будет сохранена. [10]

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

Для таблиц Наборы и Подробности наборов задано каскадное обновление связанных полей . [13]

Чтобы гарантировать, что отношения между записями в связанных таблицах правильны, и что вы случайно не удалите и не измените связанные данные, Access использует систему правил, которая называется целостностью на уровне ссылок. Когда установлен флажок Cascade Update Related Fields ( Каскадное обновление связанных полей ), изменение значения первичного ключа в главной таблице автоматически обновляет соответствующее значение во всех связанных записях. Когда установлен флажок Cascade Delete Related Records ( Каскадное удаление связанных записей), удаление записи в главной таблице удаляет все связанные с ней записи из связанной таблицы. [14]

Убедитесь, что поле флажка Обеспечение целостности данных ( EnforceReferentiallntegrity) выделено, установите флажок в поле Каскадное обновление связанных полей ( Cascade Update Related Fields) и щелкните на кнопке ОК. Для таблиц Наборы и Подробности заказов задано каскадное обновление связанных полей . [15]

Руководство по проектированию реляционных баз данных. Каскадное удаление данных

Информация в статье относится к 5-й части руководства.

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

Введение.

Ближе к сути.

О внешних ключах было рассказано в переводах, останавливаться не буду на этом. Расскажу о “спутнике”.

В случае, если не знать теории, следить за связями данных придется самостоятельно. Альтернативным вариантом является возложение этой задачи на базу данных. Что же за, так названное, слежение за связями данных? Чтобы понять, нужен пример.

У нас есть какие-то вещи. Они разбросаны, их много. Мы хотим навести порядок. Порядок – это, зачастую, классификация (категоризация) и опись. Мы хотим порядка, при этом, мы умеем работать с базами данных и не хотим ничего писать на бумаге. Мы записываем все вещи “в столбик”. Далее мы просматриваем список и определяем категории к которым относятся вещи.

Пусть это часть наших вещей, остальные не рассматриваем:

книга 1
книга 2
книга 3
компьютерная мышка
клавиатура
ручка
степлер

Книга 1, книга 2, книга 3 – это книги, как ни странно.
Компьютерная мышка, клавиатура – это компьютерная периферия.
Ручка, степлер – это канцелярские принадлежности.

Мы создаем две таблицы в базе данных: categories (категории) и stuff(вещи).

1 | книги
2 | компьютерная периферия
3 | канцелярские принадлежности

stuff_id | category_id | name

1 | 1 | книга 1
2 | 1 | книга 2
3 | 1 | книга 3
4 | 2 | компьютерная мышка
5 | 2 | клавиатура
6 | 3 | ручка
7 | 3 | степлер

P.S. Изображения с habrastorage.org не отображаются.

Итого: у нас есть книги, компьютерная периферия, канцелярские принадлежности.

Мы захотели выкинуть или подарить все наши книги, не хотим видеть эти вещи, как категорию, у себя дома, нам нравятся электронные книги. Мы удаляем из таблицы категорий категорию “книги”. При этом, у нас остаются вещи из этой категории в другой таблице, мы ссылаемся на эти категории в таблице вещей. Это и называется нарушением ссылочной целостности. Казалось бы, нет у нас категории, а значит и нет книг, но записи в таблице вещей остались и вещей-то у нас много и в будущем положение дел может повториться и повторится и тогда у нас будет бардак, много лишней информации и все вытекающие последствия как в удобстве работы с нашей информацией, так и в технической части при работе с базой (напр., поиск информации). И тут приходит понимание, что нам нужно работать с двумя таблицами, следить в каких случаях связи могут быть нарушены, сломаны и совершать какие-то телодвижения и тут есть два варианта: самостоятельно делаем это или, вот тут знание – сила, мы может переложить эту головную боль на базу данных.

В рамках реляционной модели данных таблица категорий является предком, а таблица вещей – потомком. Тут все понятно, как родитель и ребенок. Более того, случаи, в которых связи могут быть сломаны, также определены (берем и пользуемся). Наш случай – не единственный.

Сломаться связи могут (если говорить “правильным” языком – ссылочная целостность может нарушиться) в следующих случаях:

обновляется внешний ключ (ссылка на идентификатор в таблице категорий) в строке-потомке. Мы обновляем категорию (цифру, идентификатор этой категории) у какой-то вещи, и ошибаемся, нет такой категории. И… имеем подвисшую в воздухе вещь.
добавляется новая строка-потомок. Добавляем новую вещь, а она не принадлежит ни одной категории. Кстати говоря, добавить категорию мы можем без вещей. У нас так устроена база данных, что вещь не может быть без категории, а категория может, она ведь не ссылается на вещь.
удаление строки-предка. Это как раз то, что было в нашем случае. Удалили категорию, а вещи остались.
обновление первичного ключа в строке-предке. Мы поменяли идентификатор категории, а на прежний идентификатор у нас ссылаются определенные вещи. Итог: часть вещей опять в подвешенном состоянии.

Средства поддержания ссылочной целостности SQL (скажу сразу, наперед, когда будет нужно – поймете; если говорить про РСУБД MySQL, то использование этих средств вместе с внешними ключами возможно только для таблиц InnoDB; внешние ключи можно искользовать в MyISAM, создавая определенную структуру даных, но тогда вся головная боль по слежению за связями ложится на пользователя) позволяют обрабатывать указанные случаи.

И вот как решаются эти проблемы (в порядке перечисления):

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

Читать:

Смс informer что это

Теперь два последних. Тут положение дел более интересное.

Где необязательные конструкции ON DELETE и ON UPDATE позволяют задать те самые варианты решения проблемы, которые рассмотрены выше. А эти ключевые слова именуют их:

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

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

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

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

SET DEFAULT – тут понятно из названия, что при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, в таблице-потомке соответствующим записям будет выставлено значение по умолчанию. Есть одно “НО”. В РСУБД MySQL это ключевое слово не используется.

А теперь вновь – к каскадному удалению данных. Почему именно оно на слуху? Почему спросили про него в первую очередь, не смотря на то, что оно лишь одно из. Наверное, потому, что каскадное удаление данных наиболее частое решение проблемы.

Каскадное обновление связанных списков

В опубликованной ранее статье «Справочники» кроме описания процесса и краткой теории создания справочных форм и таблиц также приводился пример использования функции AppendLookupTable , позволяющей добавлять отсутствующее значение в список. Добавление происходило автоматически после прямого ввода нужного значения в список. Однако эта функция позволяла добавлять данные только в одиночный список. На практике же довольно часто бывает необходимость подобного автозаполнения, но уже связанных (зависимых) списков. О варианте решения — доработке функции AppendLookupTable и пойдет речь в этой статье.

Для рассмотрения примера создадим три справочных таблицы:

Поле в связанной таблице

Числовое (длинное целое)

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

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

Уникальный индекс — Да

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

Поле в связанной таблице

Числовое (длинное целое)

Связываем таблицы «Заказы» и «Список товаров» соотношением один ко многим по полям «id_Заказ» (на один заказ может быть много товаров).
Теперь рассмотрим вариант создания формы — справочника. Можно двумя вариантами:

Сделать справочник «Страны». Потом два справочника: «Страны — производители» и «Производители — товары». Заполняться они должны поочередно: Сначала «Страны», потом «Страны — производители», потом «Производители — товары».
Сделать один двухуровневый справочник: «Страны — производители — товары».

Во втором случае нужно предусмотреть процедуру, препятствующую вводу данных в форму «Производители», если не заведены данные в форме «Страны». Иначе получим не связанную запись. Для этого служит процедура события формы Enter (Вход).

Private Sub subСправочникПроизводители_Enter()
If IsNull(Me.id_Страны) Then
MsgBox «Сначала укажите страну производителя», vbCritical, «admin»
Страна.SetFocus
End If
End Sub

Как видим, при попытке ввести данные в форму, когда не введен ключ от главной формы

появляется сообщение об ошибке и фокус ввода переносится на поле главной формы. Аналогичная процедура блокировки сделана и для подчиненной табличной формы на форме «Заказы».

На форме «Заказы» присутствуют два списка: Страны и Производители. Причем второй связан с первым через ссылку на него в запросе (источнике своих данных). Откройте форму «Заказы» в конструкторе и посмотрите на источник строк списка Производитель(id_Компании) . На поле id_Страны установлено условие отбора

Аналогично и на поле со списком табличной подчиненной формы «subЗаказы»

Необычное в этих ссылках — это их обработка через функцию Eval(). Но об этом чуть позже.
Справочную форму можно запускать двойным кликом по списку или по меню слева. О реализации подобного интерфейса подробно рассказывалось в статье «Справочники».

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

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

Private Sub id_Страны_NotInList(NewData As String, Response As Integer)
Set ctl = Me!id_Страны ‘устанавливаем ссылку на список «Страны»
‘ запускаем функцию каскадного добавления, и в качестве второго аргумента
‘ указываем первый список. То есть функция работает как обычная процедура
‘ добавления в один список
Response = AppendLookupTable(ctl, ctl, ctl.Text, 0)
Me.id_Страны.RowSource = Me.id_Страны.RowSource ‘обновляем список стран
Set ctl = Nothing ‘очищаем переменную
End Sub

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

If cbo.Name = cbo1.Name Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
With rst
.AddNew
rst(1) = NewData
.Update
.Close
End With

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

Private Sub id_Компании_NotInList(NewData As String, Response As Integer)
Set ctl = Me.id_Компании ‘устанавливаем ссылку на список «Компании»
Set ctl1 = Me.id_Страны ‘устанавливаем ссылку на список «Страны»
‘ запускаем функцию каскадного добавления в два списка
Response = AppendLookupTable(ctl, ctl1, ctl.Text, Nz(Me.id_Страны, 0))
Set ctl = Nothing ‘очищаем переменную
Set ctl1 = Nothing ‘очищаем переменную
Me.id_Компании.RowSource = Me.id_Компании.RowSource ‘обновляем список компаний
‘ в случае отказа от заполнения
If Response = 0 Then
Me.id_Компании = Null ‘обнуляем список компаний
id_Страны.SetFocus ‘устанавливаем фокус на список страны
End If
End Sub

А теперь по поводу Eval() . Дело в том, что процедура добавления отсутствующего значения в таблицу — источник списка происходит при помощи объектной модели DAO . Если в строке запроса встретится выражение типа Forms!Заказы!id_Страны — будет сообщение об ошибке типа: «Требуется параметр». Потому, что DAO , знать ничего не знает, об открытых формах, именно по этому и ругается. Ему дали строку SQL , он пытается открыть соответствующий рекордсет. Пытается найти поле таблицы с именм Forms! и разумеется не находит.

Поэтому, когда используется работа с запросом при помощи DAO , то ссылки на элементы форм нужно оформлять через Eval() . Например так:

Или вместо имени приводить сам текст запроса, и в нем указывать ссылку:

Set rst = CurrentDb.OpenRecordset(«SELECT Производители.id_Компании, » & _
«Производители.Компания, Производители.id_страны » & _
«FROM Производители » & _
«WHERE Производители.id_Страны ORDER BY Производители.Компания»)

Эти рекомендации справедливы и при работе с объектной моделью ADO. Часто первое, на чем спотыкаются те, кто впервые решил перевести свой проект с mdb на adp — это ошибки при выполнении запросов, где есть ссылки на элементы формы. Ведь теперь обработка данных происходит на сервере, на котором нет никаких форм. Впрочем, это уже совсем другая тема, к данной статье не имеющая отношения — переход от mdb к adp.

В заключение остановлюсь еще на одном вопросе, который так же часто задают начинающие разработчики: как отключить стандартные сообщение Access? Например, при использовании процедуры на удаление записи

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

если пользователь нажмет «Отмена» будет сообщение: «Прервано выполнение макрокоманды DoMenuItem».
Избавиться от него можно по разному. Например, «оградить» эту процедуру вызова командами отключения/включения стандартных сообщений Access.

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True

Но в этом случае отключаются все сообщения, а не только «Прервано выполнение макрокоманды DoMenuItem». И если при выполнении «огражденной» процедуры возникнет какая либо другая ошибка — никто об этом «не узнает». А ведь ошибки бывают и фатальными, с «вылетом» из программы. Еще хуже, если увлекшись включением/отключением стандартных сообщений разработчик забудет потом в коде программы включить их.

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

Function sDeleteRecord() As Boolean
On Error GoTo Err_
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
sDeleteRecord = True
Exit_:
Exit Function
Err_:
ErrNum = Err.Number
sDeleteRecord = False
Err.Clear
Resume Exit_
End Function

Для ее работы в глобальном модуле Constants введена переменная Public ErrNum As Long. Через нее передается номер ошибки. Сама же процедура удаления записи выглядит так (для формы «СПРАВОЧНИК страны производители товары»):

предыдущая запись

Что такое каретка в программировании

следующий пост