2.3 Data Types, Field Properties, Primary Keys & Indexes

Once you understand that a table is built from fields, the next question is: what kind of data can a field hold, and what rules can you attach to it? This section covers ICDL skill set 1.2, items 1.2.4 through 1.2.7 — data types, field properties, primary keys, and indexes. These four concepts are tested constantly throughout the module, both as standalone theory questions and as the foundation for the hands-on table design tasks in chapter 4.

Data Types (1.2.4)

Every field in a table is assigned a data type, which tells the database exactly what kind of value that field is allowed to store and how it should be handled internally (for calculations, sorting, or display). The ICDL syllabus expects you to recognise the following common data types:

Choosing the correct data type matters: a field storing phone numbers is normally set to Text rather than Number, because phone numbers can start with a leading zero and are never used in a calculation. A field storing a price, by contrast, should be Currency or Number so it can be totalled and formatted correctly.

Field Properties (1.2.5)

A field property is an additional setting applied to a field, on top of its data type, that controls how the field behaves or displays. Common field properties include:

• Field size — the maximum length or numeric range a field can hold (for example, limiting a Text field to 50 characters, or setting a Number field's size to control the range of values it accepts).
• Format — how a value is displayed, such as showing a Currency field with a rand sign and two decimal places, or a Date/Time field as dd/mm/yyyy.
• Default value — a value automatically entered into the field for every new record unless the user changes it (for example, defaulting a Country field to "South Africa").
• Validation rule / required setting — a rule that restricts what can be entered, such as requiring a Price field to be greater than or equal to zero, or marking a field as required so a record cannot be saved with it left blank.

Field properties and data types work together but are not the same thing: the data type sets the fundamental category of the value, while field properties fine-tune how that value is sized, displayed, defaulted, or validated.

Primary Key (1.2.6)

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. No two records in the table can share the same primary key value, and a primary key field cannot be left blank. A CustomerID field using the AutoNumber data type is a classic example of a primary key — the database assigns each new customer a unique number automatically, guaranteeing there is never a duplicate.

Primary keys matter for more than uniqueness: they are also what makes it possible to reliably connect one table to another, which is the subject of the next section.

Index (1.2.7)

An index is a structure applied to a field to speed up searching and sorting on that field. Think of it like the index at the back of a textbook: instead of scanning every page to find a topic, you look it up in the index and jump straight to the right page. A database applies the same principle — indexing a frequently searched field (such as a customer's surname) lets the database engine locate matching records far faster than scanning the entire table row by row.

It is worth keeping the distinction clear for the exam:

• A primary key must be unique and is used to identify and link records.
• An index exists purely to improve search and sort performance and can be applied to a field that allows duplicate values (such as a surname, where more than one customer can share the same last name).

In Access-based database applications, a table's primary key field is typically indexed automatically, but you can also add an index to any other field you search or sort on frequently — for example, indexing a Surname field on a large Customers table so that filtering by last name stays fast even as the table grows to thousands of records.

For the Exam

Watch for exam questions that test whether you can tell a data type apart from a field property, or a primary key apart from an index. A quick way to keep them straight: data type = what kind of value; field property = how that value behaves; primary key = uniquely identifies; index = speeds up finding.