Предотвращение дублирования значений с помощью индексов

 

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

Но здесь есть подводный камень. Как вы узнали из главы 2, самый надежный способ — создание идентификационного поля (поля Код) для первичного ключа. До сих пор во все таблицы, которые вы видели, включалось такое поле. Но что, если вам нужно, чтобы другие поля тоже были уникальны? Представьте себе, что вы создаете таблицу Employees (сотрудники). Вы следуете правильным принципам проектирования БД и идентифицируете каждую запись автоматически генерируемым идентификационным номером. Но вы также хотите быть уверены в том, что в таблице нет двух сотрудников с одинаковыми номерами социального обеспечения (Social Security number, SSN), и, тем самым, желаете предупредить возможные ошибки — такие, как случайный повторный ввод данных об одном и том же со­труднике.

Примечание

Для того чтобы вспомнить, почему так важны идентификационные поля, еще раз прочитайте примечание "На профессиональном уровне. Как Access предотвращает дублирование записей" в разд. "Первичный ключ" главы 2. В таблице Employees вы конечно можете выбрать SSN в качестве первичного ключа, но ситуация будет далека от идеальной, когда вы начнете связы­вать таблицы друг с другом (см. главу 5), и возникнут проблемы, если позже понадобится из­менить номер социального обеспечения (например, из-за ошибки) или ввести информацию о сотруднике до того, как вы получите SSN.

 

Вы можете заставить поле требовать уникальных значений с помощью индекса. Индекс БД похож на предметный указатель в книге — это список значений (из поля) с перекрестной ссылкой, которая указывает на соответствующий раздел (полную запись). Если индексиро­вать поле SocialSecurityNumber, программа Access создаст список, подобный приведенному в табл. 4.1 и хранящийся в файле вашей БД.

Таблица 4.1. Список, хранящийся в БД после создания индекса

SocialSecurityNumber	Location of Full Record
001-01-3455	…
001-02-0434	…
001-02-9558	…
002-40-3200	…

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

 

 

На профессиональном уровне.

Как работают индексы

Важно то, что список номеров социального обеспечения отсортирован. Сортировка означает, что номер 001-01-3455 всегда предшествует в индексе номеру 002-40-3200, независимо от физического размещения записи в БД. Такая сортировка важна, т. к. она позволяет программе Access быстро проверять наличие дубликатов. Если вы вво­дите номер 001-02-4300, Access достаточно прочитать только первую часть списка. Как только программа обнаружит следующий "больший" SSN (тот, который включен в список позже в результате сортировки, например 001-02-501), она уже знает, что в оставшейся части индекса нет дубликата. На практике все БД используют множество алгоритмов оптимизации для того, чтобы сделать этот процесс стремительным. Но существует один ключевой принцип — без применения индекса программа Access должна проверять всю таблицу. В БД хранятся несортированные таблицы, поэтому программа не может быть уверена в том, что данного SSN нет в таблице до тех пор, пока не проверит каждую запись.

Итак, как применить индекс с полем? Хитрость заключается в применении свойства Ин­дексированное поле (Indexed), которое доступно для данных всех типов за исключением типа Вложение и типа Объект OLE. Когда вы добавляете поле, у его свойства Индексированное

поле указано значение Нет. Для вставки индекса и предупреждения дублирования значений вы можете изменить в Конструкторе значение свойства Индексированное поле

на Да (Совпадения не допускаются). При выборе третьего варианта — Да (Допускаются сов­падения) — создается индекс, но разрешается нескольким записям иметь одинаковые значе­ния поля. Этот вариант не поможет вам поймать повторяющиеся записи, но его можно при­менить для ускорения поиска (для получения дополнительной информации см. примечание "Практические занятия для опытных пользователей. Как индексы ускоряют поиск" в разд. "Получение заданного количества первых записей" главы 6).

 

Примечание

Как вы знаете из главы 2, первичные ключи также предотвращают дублирование записей с помощью аналогичного метода. Когда вы определяете первичный ключ, программа Access создает индекс в поле.

 

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

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

 

 

Часто задаваемый вопрос.

Индексы и производительность

Индексы — это средство предотвращения ввода неверных данных или средство, повышающее производительность?

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

Если вы выполняете поиск для удаления из таблицы Employees сотрудника с заданным номером социального обеспечения (SSN), Access может применить индекс. С его по­мощью программа найдет совпадающее значение гораздо быстрее и просто перейдет по указателю к полной записи.

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

 

 

Индексы для нескольких полей

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

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

 

 

Примечание

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

 

Для обеспечения уникальности комбинации полей необходимо создать составной ин­декс, в котором объединяется информация из нескольких полей. Далее описаны необходи­мые для этого действия. 1. В Конструкторе выберите на ленте Работа с таблицами | Конструктор → Показать или скрыть → Индексы (Table Tools | Design → Show/Hide → Indexes).

На экране появится окно Индексы (Indexes) (рис. 4.6). С его помощью можно просмот­реть уже созданные индексы и создать новые.

 

Рис. 4.6. В окне Индексы показаны все индексы, которые определены в таблице.

В нем приведен простой индекс для поля ID (создаваемого программой Access автоматически)

и составной индекс, который в данный момент создается

2. Выберите имя для вашего индекса. Введите его в первую пустую строку в столбце Индекс (Index Name)

Имя индекса не важно — программа Access использует его для хранения индекса в БД, но вы не увидите его во время работы с таблицей. Обычно для этого используются имена од­ного или нескольких полей, которые индексируются (например, LastName+FirstName).

3. Выберите первое поле в столбце Имя поля (Field Name) в той же строке (например, LastName).

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

4. В нижней части окна установите значение свойства Уникальный индекс (Unique) равным Да. В этом случае создается индекс, запрещающий совпадения значений (в отличие от индекса, применяемого только для увеличения скорости поиска).

Вы также можете задать значение Да для свойства Пропуск пустых полей (Ignore Nulls), если хотите, чтобы программа Access разрешила дублирование пропущенных (незаданных) значений. Например, вы хотите сделать поле номера социального обеспе­чения (SSN) необязательным. Но если уж SSN вводится вы хотите быть уверенным в том, что он не совпадает ни с каким другим значением, в данном случае вам следует за­дать значение Да в свойстве Пропуск пустых полей. Если значение этого свойства равно Нет, программа Access разрешит только одной записи иметь пропущенное (неопреде­ленное) значение в поле SSN, что, возможно, вас не устроит.

 

Примечание

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

 

Пропустите свойство Ключевое поле (Primary), которое задает индекс первичного ключа.

5. Перейдите на одну строку ниже. Оставьте поле Индекс пустым (это говорит программе Access о том, что это часть предыдущего индекса), но выберите другое поле в столбце Имя поля (например, FirstName).

Если вы хотите создать составной индекс из нескольких полей (больше двух), просто по­вторяйте этот шаг до тех пор, пока не вставите все нужные поля. На рис. 4.7 показано, как выглядит сформированный индекс. Теперь можно закрыть окно Индексы.

 

Рис. 4.7. Здесь представлен составной индекс, препятствующий включению в таблицу двух людей с одинаковыми именем и фамилией

 

Маски ввода

Как вы уже знаете, БД ценят непротиворечивость данных. Если у вас есть поле Height (рост), лучше использовать в нем значения, заданные в одних и тех же единицах измерения,

в противном случае ваши данные и ломаного гроша стоить не будут. Аналогично, если у вас есть поле PhoneNumber (номер телефона), лучше убедиться в том, что у всех номеров один и тот же формат. Если одни телефонные номера записаны с дефисами, пробелами и скобка­ми (например, [phone]), в то время как другие несколько отличаются (скажем, 847-547-1123), а третьи вообще пропускают междугородний код (547-1123), у вас появится не­большая проблема. Из-за недостатка согласованности вам будет трудно обрабатывать такие данные (например, искать конкретный телефонный номер или отсортировать телефонные номера в зависимости от междугороднего кода).

 

Рис, 4.8. Вверху: показано поле PhoneNumber с маской, готовое принимать данные. К этому моменту пользователь, вводящий данные, еще не ввел ни одного символа. В поле PhoneNumber автоматически выводится текст, содержащий символы-заполнители. Внизу: маска форматирует номера по мере их ввода. Если вы введете 1234567890 в данную маску телефонного номера, то увидите следующий текст: [phone]. В файле БД данные хранятся как 1234567890, а на листе данных представлены с привлекательным внешним оформлением. Это оформление и есть маска

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

способ ввода и редактирования значений, делая их более понятными и менее подверженны­ми ошибкам. На рис. 4.8 показано, как маска позволяет программе Access форматировать последовательность символов в процессе их ввода в поле.

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

■ Маски управляют элементом ввода. Будучи пустым, шаблон маски отображает символы-заполнители, на место которых должны попасть значения. В пустой маске телефонного номера отображается текст (_ _ _)_ _ _ - _ _ _, ясно обозначающий вид необходимых

данных.

■ Маски помогают понять смысл данных. Гораздо легче читать множество значений, представленных определенным образом. Большинство людей быстрее найдут нужные номера социального обеспечения, если они будут представлены как (012-86-7180), не как (012867180).

■ Маски предупреждают ошибки. Они отбрасывают символы, не соответствующие шаблону. Если вы пользуетесь маской для ввода номеров телефонов, то не сможете ввести буквы.

■ Маски устраняют путаницу. Одни и те же данные многих типов можно представить несколькими способами. Вы можете ввести номера телефонов с междугородним кодом и
без него. Используя маску с символами-заполнителями для междугороднего кода, выдаете понять, что эта информация обязательна (а также показываете, где она должна располагаться). Очевидно, что вам не нужно набирать скобки или дефисы для разделения номеров, поскольку эти детали уже стоят в нужном месте. Такие же преимущества маски дают при вводе дат, которые можно ввести разнообразными способами (Год/Месяц/День, Месяц-День-Год и т. д.).

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

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

 

 

Примечание

Маски поддерживают только типы данных Текстовый и Дата/время.

 

Применение готовых масок

Легче всего начать использование масок с применения одной из разнообразных подготов­ленных масок, предлагаемых программой Access. Это замечательный способ, т. к. не требует изучения таинственного искусства создания масок.

Далее перечислены действия, необходимые для выбора встроенной маски.

1.
В Конструкторе выберите текстовое поле, в котором вы хотите применить маску.
В данном примере используйте поле PhoneNumber.

2. Найдите свойство поля Маска ввода (Input Mask), щелкните кнопкой мыши в поле этого свойства.

В этот момент у правого края поля появится маленькая кнопка (...) со скругленными уг­лами, показанная на рис. 4.9.

 

Рис. 4.9. С помощью кнопки (...) со скругленными углами программа Access сообщает о том, что вы не должны вводить значение вручную. Вместо этого можно щелкнуть мышью кнопку и вывести на экран программу-мастер (например, Мастер создания масок ввода) или другую разновидность полезного диалогового окна

 

3. Щелкните мышью эту кнопку.

Запустится Мастер создания масок ввода (рис. 4.10).

4. Выберите нужную вам маску из списка возможных вариантов.

В данном случае выберите первый элемент списка (Phone Number).

 

 

Примечание

Не нравится то, что получается? Тогда вам нужно создать собственную маску, пользуясь сове­тами из разд. "Создание собственной маски" далее в этой главе. Если вы нашли близкую, но не идеальную готовую маску, выберите ее. Вы сможете откорректировать ее во втором окне данного мастера.

 

 

5. Щелкните мышью кнопку Далее (Next).

На экране появится второе окно мастера (рис. 4.11).

6. Если хотите, можно изменить маску или символ-заполнитель в ней.

Для изменения маски вам придется узнать, что означает каждый символ маски. Вы най­дете их описание в табл. 4.2.

Символы-заполнители применяются для обозначения пустых позиций, в которые вво­дится информация. Стандартный заполнитель — символ подчеркивания (_). Иногда

можно использовать пробел, дефис, звездочку или любой другой символ, введя его в ио­ле Заполнитель (Placeholder character).

 

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

 

Рис. 4.11. Для телефонного номера применяется маска!(999) 000-000. Каждая цифра 9 обозначает необязательную цифру от 0 до 9. Каждая цифра 0 представляет обязательную цифру от 0 до 9. Итак, в соответствии с данной маской [phone] — правильный номер телефона, как и 123-4567, а номер (123) 456 — неправильный

7. Щелкните мышью кнопку Далее.

Если вы вставляете маску в текстовое поле, на экран выводится завершающее окно мас­тера (рис. 4.12).

Если вы включаете маску в поле с датами, программе Access не нужно знать, как вы буде­те хранить информацию в поле — она уже знает это. В этом случае вы можете перейти к пункту 9 и щелкнуть мышью кнопку Готово (Finish).

 

Рис. 4.12. В завершающем окне мастера можно выбрать способ хранения данных в вашем поле — с символами маски или без них

8. Укажите, как вы хотите хранить значение в данном поле.

Обычно хранятся только вводимые символы (другими словами, все, что вы вводите в по­ле). Если вы выбираете этот вариант, символы-заполнители в поле не включаются. На­пример, телефонный номер [phone] хранится как 4161234567. Этот вариант эко­номит немного дискового пространства и позволяет вам изменить маску в дальнейшем для несколько иного представления данных.

Вы могли бы сохранить маску полностью со всеми дополнительными символами. В этом случае номер телефона хранился бы с дефисами, знаками подчеркивания и пробелами, например, [phone]. Это менее гибкий подход, поскольку не позволяет изменять маску впоследствии.

9. Щелкните мышью кнопку Готово.

Окончательная маска выводится в поле свойства Маска ввода (Input Mask). Прежде чем двигаться дальше, возможно, вы хотите убедиться в том, что зарезервиро­ванная длина поля соответствует маске. В примере с номером телефона Размер поля должен быть 10, если выбрано хранение неформатированного значения (поскольку в но­мере 10 цифр), или 14, если выбрано хранение полной маски вместе с заполнителями (один дефис, один пробел и две скобки).

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

 

 

Примечание

Программа Access использует информацию маски ввода для управления способом ввода дан­ных в таблицу. Но маску можно перехитрить и ввести данные по-другому. Вы могли бы создать форму (как описано в части IV) и отключить маску. Маска не обеспечивает стопроцентной за­щиты от некорректных данных, если вам нужна полная гарантия, вместо маски нужно применять правило верификации (validation rule).

 

 

Создание собственной маски

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

Создать маску очень легко, но придется потратить немного времени, прежде чем вы добьетесь желаемого результата. У вас есть два основных варианта:

■ наберите или отредактируете маску непосредственно в поле свойства Маска ввода;

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

В любой маске есть три типа символов:

■ заполнители указывают вам, куда вводить символ;

■ специальные символы сообщают программе Access о способе интерпретации части маски;

■ литералы и любые другие символы служат элементами оформления, которое облегчает трактовку значения.

В предыдущем примере маска номера телефона —!(999) 000-000. Символы 9 и 0 — запол­нители: они указывают, куда вводить цифры номера телефона. Скобки, пробел и дефис — про­сто средства форматирования — литералы. И всего один специальный символ — восклица­тельный знак. Он сообщает Access о том, что символы должны вводиться в маску слева направо, стандартный и единственный имеющий смысл в случае телефонного номера вариант.

Для того чтобы разложить все по полочкам, обратите внимание на следующие таблицы, В табл. 4.2 приведены все заполнители, которые можно использовать в масках ввода. В табл. 4.3 перечислены другие специальные символы. Все остальное автоматически относится к лите­ралам.

Таблица 4.2. Символы-заполнители для масок ввода

 

Символ	Описание
	Обязательная цифра (от 0 до 9)
	Необязательная цифра (от 0 до 9)
#	Необязательная цифра, знак плюс (+) или знак минус (—)

Символ	Описание
L	Обязательная буква
?	Необязательная буква
А	Обязательные буква или цифра
а	Необязательные буква или цифра
&	Обязательный символ любого типа (включая буквы, цифры, знаки пунктуации и т. д.)
С	Необязательный символ любого типа (включая буквы, цифры знаки пунктуации и т. д.)

Таблица 4.2. (окончание)

 

 

Таблица 4.3. Специальные символы для масок ввода

Символ	Описание
!	Обозначает направление заполнения маски слева направо при вводе. Это направле­ние выбрано по умолчанию, поэтому данный символ не требуется (но во встроенные маски он включен)
<	Преобразует все следующие за ним символы в строчные
>	Преобразует все следующие за ним символы в прописные
\	Указывает на то, что следующий символ надо интерпретировать как литерал. Например, у символа # специальное назначение в масках. Поэтому если вы хотите обычный символ # включить в маску, следует ввести \#. Иногда этот символ приме­няется перед заполнителем, даже когда он не нужен. Вы можете встретить маску телефонного номера, содержащую последовательность \- вместо просто знака -. Оба эти варианта равнозначны
Пароль (Password)	Создает поле пароля. Любой символ, который вы вводите в поле, хранится как сим­вол, но отображается как звездочка (*). С помощью варианта можно вставить в мас­ку и что-то другое

Далее приведено несколько примеров масок, чтобы помочь вам взяться за дело.

■ [phone]. В телефонный номер обязательно должны быть включены цифры междугороднего кода. Эта маска отличается от маски телефонного номера предлагаемой Мастером создания масок. В последней первые три 0 заменены 9, что делает междугородний код необязательным.

■ 00000-9999. Американский почтовый индекс, который состоит из пяти обязательных цифр, за которыми следуют дефис и (иногда) четыре дополнительные цифры.

■ L0L 0L0. Британский или канадский почтовый код, который формируется из шести символов с чередованием букв и цифр, например, M6S ЗН2.

■ 99:00:00 >LL. Маска для ввода времени в поле типа Дата/время. Она формируется из двух цифр для часов и двух цифр для минут. Последние два символа (благодаря наличию символа >) всегда отображаются как прописные и предназначены для обозначения половины суток AM или РМ. (Технически эта маска не препятствует вводу в эти позиции других символов. Но если вы введете время, такое как 12:30 GM, программа Access

пожалуется на то, что не может преобразовать ваше значение в тип данных Дата/время, как того требует поле.)

■ 099.099.099.099. IP-адрес, идентифицирующий компьютер в сети. Он записан как четыре значения, разделенные точками. В каждой части адреса должна быть, как минимум, одна цифра, а как максимум — три. Такой шаблон в маске отображается комбинацией 099 (одна обязательная цифра, за которой следуют две необязательные).

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

Маски могут заканчиваться двумя необязательными элементами, разделенными точкой с запятой (;).

Вторая составляющая маски — число, сообщающее программе Access, должна ли она со­хранять литеральные символы маски в записи БД. (Это последний вопрос, который задает Мастер создания масок.) Если этот фрагмент маски пропустить или использовать цифру 1, Access сохраняет только символы, которые вводит пользователь. Если же вы примените цифру 0, программа сохранит весь текст вместе с литералами.

В третьей составляющей маски содержится символ-заполнитель. Если этот компонент маски пропустить, программа Access применяет знакомый знак подчеркивания.

Далее приведена маска, в которую включены оба дополнительных компонента:

[phone];1;#

Во второй части стоит 1, а в третей — #. Маска предназначена для ввода телефонных но­меров и сохранения их в БД вместе с литералами маски (в данном случае двумя скобками, пробелом и дефисом), в ней вместо знака подчеркивания в качестве заполнителя использу­ется знак номера (#).

 

Рис. 4.13. Для добавления вашей собственной маски используйте кнопки переходов между записями (расположенные у нижнего края этого окна) для перехода в конец. Это окно можно использовать и для изменения маски. Например, встроенная маска телефонного номера не требует обязательного включения междугороднего кода. Если это свобода, которой вы не хотите пользоваться, замените маску более строгой версией [phone]