Изобретение велосипеда или об обеспечении целостности данных на примере таблиц-справочников в СУБД Ms Access
Ожидает приглашения
Благодарности: спасибо palarm'у, подтолкнувшему меня на написание этого топика, KrukVN'у, в комментариях к топику которого, мы начали обсуждение этой темы и конечно, Андрею Митину aka am, за, пожалуй, лучший русскоязычный сайт по программированию в Access.
Сначала цитата (в данном случае можно заменить слово «кортеж» на «запись», а слово «отношение» — на «таблица»): Трактовка реляционной модели данных согласно Дейту. "… реляционная модель состоит из трех частей, описывающих разные аспекты реляционного подхода: структурной части, манипуляционной части и целостной части…
… в целостной части реляционной модели данных фиксируются два базовых требования целостности, которые должны поддерживаться в любой реляционной СУБД. Первое требование называется требованием целостности сущностей… Конкретно требование состоит в том, что… любое отношение должно обладать первичным ключом…
Второе требование называется требованием целостности по ссылкам и является несколько более сложным…
Требование целостности по ссылкам, или требование внешнего ключа состоит в том, что для каждого значения внешнего ключа, появляющегося в ссылающемся отношении, в отношении, на которое ведет ссылка, должен найтись кортеж с таким же значением первичного ключа, либо значение внешнего ключа должно быть неопределенным (т.е. ни на что не указывать)…
… Понятно, что при обновлении ссылающегося отношения (вставке новых кортежей или модификации значения внешнего ключа в существующих кортежах) достаточно следить за тем, чтобы не появлялись некорректные значения внешнего ключа. Но как быть при удалении кортежа из отношения, на которое ведет ссылка?
Здесь существуют три подхода, каждый из которых поддерживает целостность по ссылкам. Первый подход заключается в том, что запрещается производить удаление кортежа, на который существуют ссылки (т.е. сначала нужно либо удалить ссылающиеся кортежи, либо соответствующим образом изменить значения их внешнего ключа). При втором подходе при удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится неопределенным. Наконец, третий подход (каскадное удаление) состоит в том, что при удалении кортежа из отношения, на которое ведет ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи.
В развитых реляционных СУБД обычно можно выбрать способ поддержания целостности по ссылкам для каждой отдельной ситуации определения внешнего ключа"(конец цитаты). («Основы современных баз данных», взято с ЦИТ-форума).
Access, являясь реляционной СУБД, не может дать больше этих трех вариантов действий для поддержания целостности по ссылкам при удалении записи из «справочника». То есть когда Вы устанавливаете флажок «обеспечение целостности данных» при редактировании связи в схеме данных, то не сможете удалить запись из справочника не удалив соответствующие записи из таблицы, содержащей внешние ключи, указывающие на первичный ключ удаляемого значения. При снятии флажка — внешний ключ устанавливается в неопределенное значение (указывает в пустоту). Если добавить флажок «каскадное удаление», то именно оно и будет происходить.
Чем это грозит пользователю? Например, он будет видеть пустые поля там где неделю назад вводил данные с помощью справочника, а сегодня эту запись из справочника удалил (в случае невыставленного флажка «целостности данных»). Или просто пользователь потеряет значительную (если не всю) часть базы данных (в случае выставления флажка «каскадное удаление» (маловероятно, но возможно). Выходов как всегда несколько:
1 — «перевод стрелок». Объяснить пользователю, что «вот ты снес запись из справочника и поэтому в более ранних записях теперь остались пустые поля и ничего уже не сделаешь» или «ты удалил запись из справочника и поэтому у тебя стерлась половина базы данных».
2 — «прозрачный». Сохранять удаляемые значения как-то и где-то, для того, чтобы удаление записи из справочника оставляло старые записи, использующие удаляемую запись, в прежнем виде.
3 — «кнут и пряник». Запретить удаление из справочников, оставив возможность добавления в них.
4 — «дисциплинирующий». Вообще запретить удаление и добавление из/в справочников.
5 — «альтернатива». С точки зрения удобства для пользователя (заказчика), мне кажется, лучший вариант это — предоставить пользователю возможность выбора того, что может программа сделать со связанными данными при удалении первичного ключа из справочника (с доходчивым объяснением происходящего).
Наверняка есть еще несколько способов, но я решил реализовать 5-й + 2-й способы. В прилагаемом файле — то, как я себе это представляю. Код немного сыроват (кое-какие вещи можно было бы оформить в виде функций, можно было бы поактивней использовать циклы, SQL и т.д.), но все работает.
При «удалении» значения из справочника оно может помечаться как удаленное, оставаясь в той же таблице (при вводе новых данных в справочном списке его уже не будет, но при просмотре и редактировании записей использующих его, они все будут доступны), может удаляться несколькими способами.
P.S. Возможность добавления нового справочника и ввода в него данных есть, но я не включил в пример поддержку интерфейса для него (новый справочник просто некуда будет «воткнуть»). Удалять справочник также можно. При этом справочные данные помечаются как удаленные, их внешний ключ (указывающий на первичный ключ в «справочнике справочников») — обнуляется. То есть я намеренно не включил в процедуру удаления справочника код для проверки существующих ссылок и т.д., чтобы не загромождать модули. Вся основная работа показана на примере удаления значений из справочников…
P.P.S. используется рекордсет из библиотеки DAO.
P.P.P.S файл примера в Access 2003:
Сначала цитата (в данном случае можно заменить слово «кортеж» на «запись», а слово «отношение» — на «таблица»): Трактовка реляционной модели данных согласно Дейту. "… реляционная модель состоит из трех частей, описывающих разные аспекты реляционного подхода: структурной части, манипуляционной части и целостной части…
… в целостной части реляционной модели данных фиксируются два базовых требования целостности, которые должны поддерживаться в любой реляционной СУБД. Первое требование называется требованием целостности сущностей… Конкретно требование состоит в том, что… любое отношение должно обладать первичным ключом…
Второе требование называется требованием целостности по ссылкам и является несколько более сложным…
Требование целостности по ссылкам, или требование внешнего ключа состоит в том, что для каждого значения внешнего ключа, появляющегося в ссылающемся отношении, в отношении, на которое ведет ссылка, должен найтись кортеж с таким же значением первичного ключа, либо значение внешнего ключа должно быть неопределенным (т.е. ни на что не указывать)…
… Понятно, что при обновлении ссылающегося отношения (вставке новых кортежей или модификации значения внешнего ключа в существующих кортежах) достаточно следить за тем, чтобы не появлялись некорректные значения внешнего ключа. Но как быть при удалении кортежа из отношения, на которое ведет ссылка?
Здесь существуют три подхода, каждый из которых поддерживает целостность по ссылкам. Первый подход заключается в том, что запрещается производить удаление кортежа, на который существуют ссылки (т.е. сначала нужно либо удалить ссылающиеся кортежи, либо соответствующим образом изменить значения их внешнего ключа). При втором подходе при удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится неопределенным. Наконец, третий подход (каскадное удаление) состоит в том, что при удалении кортежа из отношения, на которое ведет ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи.
В развитых реляционных СУБД обычно можно выбрать способ поддержания целостности по ссылкам для каждой отдельной ситуации определения внешнего ключа"(конец цитаты). («Основы современных баз данных», взято с ЦИТ-форума).
Access, являясь реляционной СУБД, не может дать больше этих трех вариантов действий для поддержания целостности по ссылкам при удалении записи из «справочника». То есть когда Вы устанавливаете флажок «обеспечение целостности данных» при редактировании связи в схеме данных, то не сможете удалить запись из справочника не удалив соответствующие записи из таблицы, содержащей внешние ключи, указывающие на первичный ключ удаляемого значения. При снятии флажка — внешний ключ устанавливается в неопределенное значение (указывает в пустоту). Если добавить флажок «каскадное удаление», то именно оно и будет происходить.
Чем это грозит пользователю? Например, он будет видеть пустые поля там где неделю назад вводил данные с помощью справочника, а сегодня эту запись из справочника удалил (в случае невыставленного флажка «целостности данных»). Или просто пользователь потеряет значительную (если не всю) часть базы данных (в случае выставления флажка «каскадное удаление» (маловероятно, но возможно). Выходов как всегда несколько:
1 — «перевод стрелок». Объяснить пользователю, что «вот ты снес запись из справочника и поэтому в более ранних записях теперь остались пустые поля и ничего уже не сделаешь» или «ты удалил запись из справочника и поэтому у тебя стерлась половина базы данных».
2 — «прозрачный». Сохранять удаляемые значения как-то и где-то, для того, чтобы удаление записи из справочника оставляло старые записи, использующие удаляемую запись, в прежнем виде.
3 — «кнут и пряник». Запретить удаление из справочников, оставив возможность добавления в них.
4 — «дисциплинирующий». Вообще запретить удаление и добавление из/в справочников.
5 — «альтернатива». С точки зрения удобства для пользователя (заказчика), мне кажется, лучший вариант это — предоставить пользователю возможность выбора того, что может программа сделать со связанными данными при удалении первичного ключа из справочника (с доходчивым объяснением происходящего).
Наверняка есть еще несколько способов, но я решил реализовать 5-й + 2-й способы. В прилагаемом файле — то, как я себе это представляю. Код немного сыроват (кое-какие вещи можно было бы оформить в виде функций, можно было бы поактивней использовать циклы, SQL и т.д.), но все работает.
При «удалении» значения из справочника оно может помечаться как удаленное, оставаясь в той же таблице (при вводе новых данных в справочном списке его уже не будет, но при просмотре и редактировании записей использующих его, они все будут доступны), может удаляться несколькими способами.
P.S. Возможность добавления нового справочника и ввода в него данных есть, но я не включил в пример поддержку интерфейса для него (новый справочник просто некуда будет «воткнуть»). Удалять справочник также можно. При этом справочные данные помечаются как удаленные, их внешний ключ (указывающий на первичный ключ в «справочнике справочников») — обнуляется. То есть я намеренно не включил в процедуру удаления справочника код для проверки существующих ссылок и т.д., чтобы не загромождать модули. Вся основная работа показана на примере удаления значений из справочников…
P.P.S. используется рекордсет из библиотеки DAO.
P.P.P.S файл примера в Access 2003: