Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 36 ยท Design and Normalization

Keys, Indexes and Data Integrity in Access

Protect Access data with primary keys, composite keys, unique and multi-field indexes, referential integrity with cascade options, validation rules and required fields.

A good design only helps if the database enforces it. Access lets you push rules into the tables themselves so every form, query, import and VBA routine must obey them. This lesson covers the four tools: keys, indexes, relationships with referential integrity, and validation rules. Rules in the table are always better than rules only in forms, because a form can be bypassed and a table cannot.

Primary keys

Set a primary key in Table Design view: select the field row(s) and choose Primary Key on the ribbon (a key icon appears). Access creates a unique index called PrimaryKey and refuses duplicates or Nulls in it.

  • AutoNumber (Long Integer, increment) is the usual choice. It never repeats, but it can have gaps (a deleted or cancelled row leaves one). Never show it to users as a meaningful number such as an invoice number, because gaps look like missing invoices.
  • A composite key uses two or more fields together. Select both rows (Ctrl+click) and click Primary Key. A junction table's key is typically the pair of foreign keys, which also prevents adding the same product twice to one order.
  • For natural keys (ISBN, SKU) make sure the value is permanent and truly unique.

Indexes

An index is a sorted lookup structure that makes searches, sorts and joins faster, at a small cost for each insert and update. In Design view, set a field's Indexed property:

Setting Meaning
No not indexed
Yes (Duplicates OK) indexed, repeated values allowed (speeds WHERE, joins, sorting)
Yes (No Duplicates) a unique index: duplicates are rejected

Index fields you filter, sort or join on often: foreign keys (Access indexes them automatically when you build a relationship with integrity), dates you filter by, and codes you search. Do not index everything: small tables, fields with only a few distinct values (Yes/No) and rarely searched fields gain little.

For rules that involve several fields, open Design > Indexes and add a multi-field index, for example a unique index on (CourseID, StudentID) so a student cannot enrol twice in one course. Check Unique and Ignore Nulls as needed. The SQL for the same idea is a UNIQUE index; here is the rule in action:

SQL A unique multi-field index blocks duplicates

Press Run (or Ctrl + Enter) to see the result here.

With a plain INSERT the duplicate fails with "UNIQUE constraint failed" (the OR IGNORE above skips it, so rows_added is 0); Access shows "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" (error 3022).

Relationships and referential integrity

Open Database Tools > Relationships, add the tables and drag the primary key field of the "one" table onto the foreign key of the "many" table. In the Edit Relationships dialog tick:

  • Enforce Referential Integrity: you cannot add an order for a customer that does not exist, and you cannot delete a customer who still has orders. This is the rule that prevents orphan records.
  • Cascade Update Related Fields: if a primary key value changes (rare with AutoNumber), related rows follow.
  • Cascade Delete Related Records: deleting the parent deletes the children. Convenient but dangerous: deleting a customer silently wipes their order history. Use it only where children are meaningless without the parent (order lines of a deleted order), never for the customer-orders link.

The join type (inner, or outer joining all records of one side) set here is the default for queries. Referential integrity needs matching data types and the "one" side field must be a primary key or have a unique index. If it will not enforce, first find the existing orphans with an unmatched query:

SQL Find orphan rows before enforcing integrity

Press Run (or Ctrl + Enter) to see the result here.

In Access, Create > Query Wizard > Find Unmatched Query Wizard builds exactly this. Delete or repair the orphans, then enforce the relationship.

Field rules: Required, Validation Rule and defaults

Table Design view offers field properties that guard each value:

  • Required = Yes rejects Null. Allow Zero Length controls empty strings in text fields (set No for most fields so "empty" has one representation).
  • Default Value: Date() for an order date, 0 for quantity, "Open" for status.
  • Validation Rule: an expression the value must satisfy, with a friendly Validation Text shown on failure. Examples: >=0 for price, Between 1 And 100 for a percentage, Like "??-####" for a code pattern, <=Date() so a birth date cannot be in the future.
  • Format, Input Mask and Caption shape display and entry (>LL forces uppercase letters, 000\-0000 for phone patterns). A mask changes what the user can type, not what is stored.
  • A table-level validation rule (Table Properties) compares fields, e.g. [EndDate] >= [StartDate].

The same rules in standard SQL are NOT NULL, DEFAULT and CHECK, which you can try right away:

SQL NOT NULL, DEFAULT and CHECK

Press Run (or Ctrl + Enter) to see the result here.

Lookup fields: use with care

Access has a Lookup Wizard data type that turns a field into a combo box showing names while storing IDs. It is handy for forms but hides what is stored (you see "Ana" in the datasheet but the field holds 1), which confuses queries and exports. A good habit is to keep table fields plain, build combo boxes on forms, and show names through joins in queries.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does 'Enforce Referential Integrity' prevent?

    It requires every foreign key to match an existing primary key and blocks deleting a parent that still has children.
  2. 2. Why is Cascade Delete risky for Customers -> Orders?

    Cascade deletes remove child rows automatically; use it only where children are meaningless alone.
  3. 3. Which makes a student unable to enrol twice in the same course?

    A multi-field unique index rejects duplicate pairs.
  4. 4. A validation rule of Between 1 And 100 is stored where?

    Table-level rules protect the data from forms, queries, imports and code alike.

Summary

  • Every table needs a primary key (AutoNumber by default, composite for junction tables).
  • Index fields used in filters, sorts and joins; use unique indexes for business rules.
  • Enforce referential integrity; use cascade delete rarely; find orphans with an unmatched query first.
  • Put Required, Default, Validation Rule and table-level rules in the table; keep lookup combos on forms.

Next: Compact, repair and backup