Средства обеспечения ссылочной целостности

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

Режимы каскадного обновления и каскадного удаления

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

Выбор ключа таблицы

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

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

 

Задание 1.3.

Чтобы определить ключ таблицы "Персональные мероприятия" и проиндексировать ее по значению ключа:

1. Откройте таблицу "Персональные мероприятия" в режиме конструктора.
2. Нажмите клавишу Ctrl, и удерживая ее нажатой, выделите поле
"КодСотрудника", "ТипМероприятия" и "ДатаПлан" мышкой. Этот способ является общим способом выделения нескольких элементов в бланках и таблицах Access.

3. Нажмите кнопку "Ключевое поле" (Primary Key) на панели инструментов. Слева от каждого из выделенных полей появится маркер ключевого поля.

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

Индексирование таблицы

Хотя Access автоматически индексирует таблицу по значению ключа, может потребоваться создать дополнительные индексы по значениям других полей. Индексы позволяют ускорить поиск данных в тех полях таблицы, по которым она проиндексирована. Например, чтобы поиск записей соответствующих персональным мероприятиям за определенный период происходил быстрее, нужно проиндексировать таблицу по значению поля "ДатаПлан". Каждая таблица Access может иметь до 32 индексов, 5 из которых могут быть составными (в составной индекс может входить не более 10 полей). Тем не менее, не стоит увлекаться созданием индексов для каждого поля таблицы и всех их комбинаций, поскольку это может существенно замедлить процесс заполнения таблицы (при изменении или добавлении записи автоматически обновляются все индексы!!!).

 

Задание 1.4.

Проиндексировать таблицу "Персональные мероприятия" по значению поля "Датафакт" и создать составной индекс по значениям полей "ТипМероприятия" и "ДатаПлан":

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

2. В окне "Индексы" выводятся все индексы таблицы. Чтобы создать составной индекс, введите Тип/Дата в столбце "Индекс" и выберите поля "ТипМероприятия" и "ДатаПлан" в раскрывающихся списках столбца "Имя поля".

3. Перейдите в режим таблицы, нажав кнопку "Режим таблицы". Появится диалоговое окно, сообщающее о том, что структура таблицы была изменена. Подтвердите сохранение структуры таблицы, нажав кнопку "Да".
Теперь таблица "Персональные мероприятия" имеет три индекса: индекс, который был создан по значению ключа, простой индекс по значению поля "ДатаФакт" и составной по значениям полей "ТипМероприятия" и "ДатаПлан".

Изменение свойств полей и связей между таблицами

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

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

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

Изменение порядка следования полей

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

 

Задание 1.5.

Измените порядок следования полей в таблице "Персональные мероприятия":

1. Откройте таблицу "Персональные мероприятия" в режиме таблицы. Порядок следования полей является единственным параметром структуры таблицы, который можно изменить в режиме таблицы.

2. Чтобы выделить столбец (поле) таблицы, нажмите кнопку, на которой написано имя поля.

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

4. Удерживая нажатой левую кнопку мыши, перетащите поле на новое место и отпустите кнопку мыши.

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

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

Изменение связей между таблицами.

Определение связей между таблицами достаточно простая операция. Из- менение связи может быть вызвано изменением типа данных полей, по которым строится связь (поля, по которым строится связь, должны иметь один тип). В этом случае нужно удалить связь, изменить типы данных и переустановить связь.

 

Задание 1.6.

Переустановите связи базы данных.

1. Закройте все окна таблиц.

2. Щелкните левой кнопкой мыши по связи, которую нужно изменить. Линия выделенной связи становится толще.

3. Чтобы удалить выделенную связь, нажмите клавишу Del. Подтвердите удаление связи, нажав кнопку "Да" в появившемся диалоговом окне.

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

5. Измените типы данных полей таблиц.

6. Переустановите связи.

 

Добавление, изменение и проверка корректности данных в таблицах

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

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

Другой важной функцией, которую должна обеспечивать система управления базами данных, является поддержка целостности данных. Правила, обеспечивающие целостность данных, дополняют правила, обеспечивающие ссылочную целостность, которые описаны выше. Access позволяет определить правила, обеспечивающие целостность данных как на уровне полей, так и на уровне таблиц. Правила, обеспечивающие целостность данных, задаются в виде выражений, вводимых в свойство "Условие на значение" (Validation Rule) полей или таблиц. Далее обсуждено использование простых выражений для определения условий на значение. Более полное представление об использовании операторов и выражений Access можно получить, используя встроенный механизм контекстной помощи.

1.2.4. Проверка корректности данных

 

Выражения являются атомарными элементами языков программирования. Выражения используются для вычисления различных величин и содержат, по крайней мере, один оператор (например, арифметические операторы, операторы сравнения и т. д.). Чаще всего используются арифметические операторы +, -, * (оператор умножения), / (оператор деления). Величины, к которым применяется оператор, называются операндами. Различают левый и правый операнд оператора.

Например, в выражении 10+12 число 10 является левым операндом оператора сложения, а число 12 правым операндом.
Существуют операторы, позволяющие сравнивать значения своих операндов. Эти операторы называются операторами сравнения (например, > (знак больше), = (знак равенства) и т. д).

Логические операторы (And, Or, is, Not, Between и Like) возвращают в качестве результата одно из значений "Истина" (True), "Ложь" (False) или пустое значение (Null), если результат вычислить невозможно.
Оператор & используется для слияния (конкатенации) двух строк в одну. Этот оператор является аналогом оператора + (оператор конкатенации) в системах Paradox и xBase. В Access для слияния строк также можно использовать оператор +, но оператор & предоставляет дополнительные возможности, и поэтому предпочтительней использовать именно его. Access позволяет создавать сложные выражения из простых, комбинируя их в соответствии с синтаксисом операторов.

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

Определение условий на значение полей

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

Таблица 1.4.

Поле	Условие на значение	Текст сообщения об ошибке
КодСотрудника	>о	Введите правильный код сотрудника
ТипМероприятия	"П" Or "У" Or "К" Or "Г" Or "М" Or "Н" Or "0"	Недопустимый код. Допустимы только коды "П", "У", "М", "К", "Г", "Н" и "0"
КодОтветственного	>0	Введите правильный код сотрудника, ответственного за мероприятие
ДатаПлан	Between Date() -36500 And Date() + 365	Мероприятие не может быть запланировано более чем на один год вперед
КодНачальника	>0 Or Is Null	Введите правильный код начальника или оставьте поле пустым
Рейтинг	Between 0 And 9 Or Is Null	Введите правильный рейтинг сотрудника (число от 0 до 9) или оставьте поле пустым
Сумма	He определено	Нет
Примечание	He определено	Нет

 

Условия на значения полей "КодСотрудника", "КодОтветственного" и "КодНачальника" не могут определить, правильно ли был введен код. Например, в одно из этих полей может быть введен код, которого нет в таблице "Сотрудники". Тем не менее, поскольку при создании связи с таблицей "Сотрудники" был установлен режим обеспечения ссылочной целостности, Access не позволит сохранить запись в подчиненной таблице, которой не соответствует ни одна запись в главной таблице. Поэтому проверка корректности кода сотрудника, с которым связано мероприятие, не нужна. Для обеспечения корректности данных в полях "КодОтветственного" и "КодНачальника" необходимо проверять значения этих полей на совпадение с кодом одного из сотрудников в таблице "Сотрудники".

 

Задание 1.7.

Добавьте условия на значение полей таблицы "Персональные мероприятия":
1. Откройте таблицу "Персональные мероприятия" в режиме конструктора.
2. Выделите поле "КодСотрудника". Поместите точку вставки в поле ввода "Условие на значение", введите >0.

3. В поле ввода "Сообщение об ошибке" введите строку: "Введите правильный код сотрудника".

4. Поместите точку вставки в поле ввода "Обязательное поле" (Required) и выберите "Да" в раскрывающемся списке.

5. Перейдите в бланк структуры таблицы, выделите следующее поле. Введите условия на значение и текст сообщения об ошибке для оставшихся полей таблицы. Установите для свойства "Обязательное поле" полей "ТипМероприятия", "КодОтветственного" и "ДатаПлан" значение "Да".

Определение условий на значение записей

Значение поля "ДатаФакт" таблицы "Персональные мероприятия" должно превышать значение поля "ДатаПлан". Поэтому для проверки корректности значения поля "ДатаФакт" это значение нужно сравнить со значением поля "ДатаПлан".Для этого можно задать условие на значение записи таблицы.

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

 

Задание 1.8.

Определите условие на значение записи для поля "ДатаФакт":

1. Откройте таблицу "Персональные мероприятия" в режиме конструктора и нажмите кнопку "Свойства" (Properties) на панели инструментов. Появится диалоговое окно "Свойства таблицы".

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

4. Дважды щелкните левой кнопкой мыши по элементу "ДатаФакт" в списке полей таблицы. В окне выражения появится строка (ДатаФакт].
5. Введите >= в окне выражения и дважды щелкните левой кнопкой мыши по элементу "ДатаПлан" в списке полей таблицы, чтобы добавить его в выражение.
6. При создании таблицы "Персональные мероприятия" в поле "ДатаФакт" планировалось хранить пустые значения для мероприятий, которые еще не произошли. Поэтому добавьте к выражению, задающему условие на значение, строку Ог [ДатаФакт] Is Null.

7. Чтобы закрыть окно построителя выражений и добавить условие на значение записей таблицы, нажмите кнопку ОК.

8. Поместите точку вставки в поле ввода "Сообщение об ошибке" и введите текст сообщения об ошибке: Дата проведения мероприятия не может предшествовать запланированной дате мероприятия.

9. Закройте окно "Свойства таблицы", нажав кнопку "Закрыть" в правом верхнем углу окна или нажав кнопку "Свойства" на панели инструментов.

 

 

1.2.5. Добавление записей в таблицу "Персональные мероприятия"

 

Теперь вам предоставляется возможность проверить результаты вашей работы. В таблице 5 приведены данные о приеме на работу каждого из 9 сотрудников компании Борей, которые нужно ввести в таблицу «Персональные мероприятия». Значения полей "ДатаПлан" и "ДатаФакт" определены на основе поля "ДатаНайма" таблицы "Сотрудники". После заполнения таблицы "Персональные мероприятия" данными о приеме сотрудников на работу, поле "ДатаНайма" может быть удалено из таблицы "Сотрудники", поскольку в таблице "Персональные мероприятия" уже хранятся сведения о дате найма сотрудников.

Таблица 1.5

Код	Тип	Ответственный	Дата-План	Начальник	Сумма
	П		01-май-02		2 000000
	П		14-авг-02		3 500000
	П		Ol-anp-02		2 250000
	П		ОЗ-май-03		2 250000
	П		17-OKT-03		2 500000
	П		17-OKT-03		4 000000
	П		02-янв-04		3 000000
	П		05-мар-04		2 500000
	П		15-НОЯ-04		3 000000

 

В процессе заполнения таблицы можно проверить правильность определения значений свойств "Значение по умолчанию" и "Формат поля". Можно попробовать ввести в таблицу некорректные данные, чтобы проверить условия на значение полей и записей. Чтобы заполнить первые 9 записей таблицы "Персональные мероприятия", используйте данные из таблицы 5, откройте таблицу "Персональные мероприятия" в режиме таблицы. Установите точку вставки в первое поле хвостовой записи.

 

 

Проверка корректности данных в таблице "Персональные мероприятия"

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

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

· Ссылочная целостность. Введите в поле "КодСотрудника" хвостовой записи таблицы "Персональные мероприятия" значение 25, а в поле "КодОтветственного" значение 2 и нажмите клавишу <стрелка вниз>. Нажатие этой клавиши означает сохранение изменений в ней и выделение последней записи. При попытке сохранения изменений в новой записи Access автоматически проверяет, существует ли в таблице "Сотрудники" запись, на которую ссылается по значению поля "КодСотрудника" новая запись таблицы "Персональные мероприятия". Если такой записи не существует, то появляется сообщение об ошибке.

· Уникальность значений ключа таблицы. В только что добавленной записи введите значения полей, совпадающие с предыдущей записью, а затем нажмите клавишу <стрелка вниз>. Появится сообщение об ошибке.

· Проверка корректности значений полей. Введите в поле "КодСотрудника" значение Ы и нажмите клавишу < стрелка вправо>. Появится сообщение об ошибке, текст которого был введен в свойство "Сообщение об ошибке" поля "КодСотрудника". Нажмите кнопку ОК. Введите в поле "ТипМероприятия" значение п и нажмите клавишу < стрелка вправо>. После того как точка вставки покинет поле "ТипМероприятия", значение этого поля должно измениться на П, поскольку был использован формат ">@". В поле "КодОтветственного" введите значение «П» и нажмите клавишу <стрелка вправо>. Появится сообщение об ошибке, текст которого был введен в свойство "Сообщение об ошибке" поля "КодОтветственного". Нажмите кнопку ОК.

· Продолжайте процесс тестирования. Введите значение 01/01/80 в поля "ДатаПлан" и "Датафакт", чтобы проверить работу условия на значение поля "ДатаПлан".

 

1.3. Контрольные вопросы

1. Элементы базы данных Access.

2. Просмотр и изменение данных в таблице.
3. Основные функции и режимы Access.
4. Общие свойства таблицы.
5. Свойства полей таблицы.
6. Работа в режиме конструктора таблиц.
7. Определение значений по умолчанию для полей таблицы.
8. Работа со связями, ключевыми полями и индексами.
9. Обеспечение ссылочной целостности.
10. Определение условий на значение полей и записей.

11. Типы полей в Access.

12.Типы связей в базе данных Access.

13. Назначение макросов.

14. Назначение модулей.

15.Индексирование таблиц.

16. Обеспечение целостности данных.

17.Ключевые поля и их роль в базе данных.

18. Обеспечение ссылочной целостности.

19.Изменение порядка следования полей.

20. Определение значений по умолчанию.