Primary Keys Field Properties and Data Validation

Protect data quality with unique identifiers, required fields, indexes and validation rules.

What you will learn

• Set an appropriate primary key
• Configure useful field properties
• Create validation rules and clear messages

Before you begin

Use a practice database containing non-sensitive sample information. Access versions can place commands differently, so use the ribbon search box when a named command is not immediately visible. Back up the file before structural changes.

Identify every record

A primary key uniquely identifies each record and cannot contain duplicates. AutoNumber is convenient for an internal identifier when the organisation has no stable natural key.

Users should not assign meaning to an AutoNumber value. Its job is reliable identification, not indicating rank, count or chronology.

Set field properties

Field Size, Format, Default Value, Required and Indexed affect storage or data entry. Set them deliberately in Design view.

Use Required only for information truly needed for every record. Index fields that are searched or joined frequently, while remembering that excessive indexes can slow updates.

Validate entries

A validation rule rejects values outside an allowed condition. Validation text should explain how to correct the entry instead of merely saying Invalid.

A field input mask can guide formatting, but it does not prove that a telephone number, date or identifier is genuine.

Guided practice

1. Set CustomerID as the Customers primary key.
2. Make FamilyName required.
3. Add a sensible validation rule and explanatory message to a practice field.

Knowledge check

1. What does a primary key guarantee?
2. Should an AutoNumber encode business meaning?
3. What makes validation text useful?

Completion standard

Continue when you can complete the guided practice safely and explain the knowledge-check answers in your own words.