Blocking Bad Data - Preventing Duplicate Values with Indexes
(Page 4 of 4 )
Any table’s first rule is that each record it contains must be unique. To enforce this restriction, you need to choose a primary key (page 77), which is one or more fields that won’t ever be duplicated in different records.
Here’s the catch. As you learned in Chapter 2, the safest option’s to create an ID field for the primary key. So far, all the tables you’ve seen have included this detail. But what if you need to make sure other fields are unique? Imagine you create an Employees table. You follow good database design principles and identify every record with an automatically generated ID number. However, you also want to make sure that no two employees have the same Social Security number (SSN) to prevent possible errors—like accidentally entering the same employee twice.
Figure 4-5.
If you use the Date( ) function as the default value for the DateAcquired field in the bobblehead table, then every time you add a new bobblehead record, Access fills in the current date. You decide whether you want to keep that date or replace it with a different value.
Note: For a quick refresher about why ID fields are such a good idea, refer to page 78. In the Employees table, you certainly could choose to make the SSN the primary key, but it’s not the ideal situation when you start linking tables together (Chapter 5), and it causes problems if you need to change the SSN later on (in the case of an error), or if you enter employee information before you’ve received the SSN.
You can force a field to require unique values with an index. A database index is analogous to the index in a book—it’s a list of values (from a field) with a cross-reference that points to the corresponding section (the full record). If you index the SocialSecurityNumber field, Access creates a list like this and stores it behind the scenes in your database file:
SocialSecurityNumber | Location of Full Record |
001-01-3455 | … |
001-02-0434 | … |
001-02-9558 | … |
002-40-3200 | … |
Using this list, Access can quickly determine whether a new record duplicates an existing SSN. If it does, then Access doesn’t let you insert it.
So how do you apply an index to a field? The trick’s the Indexed field property, which is available for every data type except Attachment and OLE Object. When you add a field, the Indexed property’s set to No, which means Access doesn’t
UP TO SPEED
How Indexes Work
It’s important that the list of SSNs is sorted. Sorting means the number 001-01-3455 always occurs before 002-40-3200 in the index, regardless of where the record’s physically stored in the database. This sorting’s important, because it lets Access quickly check for duplicates. If you enter the number 001-02-4300, then Access needs to read only the first part of the list. Once it finds the next “larger” SSN (one that falls later in the sort, like 001-02-501), it knows the remainder of the index doesn’t contain a duplicate.
In practice, all databases use many more optimizations to make this process blazingly fast. But there’s one key principle—without an index, Access would need to check the entire table. Tables aren’t stored in sorted order, so there’s no way Access can be sure a given SSN isn’t in there unless it checks every record.
create a field. To add an index and prevent duplicates, you can change the Indexed property in Design view to Yes [No Duplicates]. The third option, Yes [Duplicates OK], creates an index but lets more than one record have the same value. This option doesn’t help you catch repeated records, but you can use it to speed up searches (see the box, “How Indexes Speed Up Searches” on page 195 for more).
Note: As you know from Chapter 2 (page 78), primary keys also disallow duplicates, using the same technique. When you define a primary key, Access automatically creates an index on that field.
When you close Design view after changing the Indexed field property, Access prompts you to save your changes. At this point, it creates any new indexes it needs. You can’t create a no-duplicates index if you already have duplicate information in your table. In this situation, Access gives you an error message when you close the Design window and it attempts to add the index.
FREQUENTLY ASKED QUESTION
Indexes and Performance
Are indexes a tool for preventing bad data or a technique for boosting performance?
Indexes aren’t just for preventing duplicate values. They also shine when you need to boost the speed of common searches. Access can use the index to look up the record it wants, much like you can use the index at the back of this book to find a specific topic.
If you perform a search that scours the Employees table looking for the person with a specific SSN, then Access can use the index. That way, it locates the matching entry much quicker, and simply follows the pointer to the full record.
For more information about how indexes can speed up searches, refer to page 195. However, it’s important to realize that indexes enhance performance only for extremely large, complex tables. If you’re storing a few hundred records, each of which has a handful of fields, you really don’t need an index—Access already performs searches with blinding speed.
Please check back next week for the continuation of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter four of Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Check it out today at your favorite bookstore. Buy this book now.
|
|
