Skip to main content

Index Types

Last updated 1/11/2023

Keys play an important part in your database. Five provides different types of keys such as primary, foreign, dependent, and index. A key can be a single or combination of multiple fields in a table. They can be used to uniquely identify rows, retrieve records according to conditions, optimize performance, and create relationships with other tables and data sources.

Types of Keys

We have the following keys in Five which are used to retrieve records from data sources and to make relationships with other tables and data sources.

Primary Key

A primary key is a set of one or more fields in a table that uniquely indentify a record in a table. It must be required and unique, meaning it cannot accept empty or duplicate values. Five uses a GUID (globally unique identifier) in this field to uniquely identify each record.

Primary key field
Figure 1 - Primary key field

Foreign Key

A foreign key is a field or fields in a table that refers to an index, often the primary key, in another table. Foreign keys link together two or more tables in a relational database.

Foreign relationship
Figure 2 - Foreign relationship

Dependent Key

A dependent key supports table inheritance. You may have two or more tables that have common fields, these common fields can be stored in the parent table and then children tables depend on the parent for these fields. Lets take a look at an example. The Customer and Contact tables have a number of common fields, LastName, FirstName, Address, Phone, Email.

Common fields
Figure 3 - Common fields

You can create a table to hold the common fields and give this table a type field that will hold the child table the data belongs to. The child tables, Customer and Contact, now depend on the parent table, Entity, and will inherit the fields from the Entity table.

Inheritance
Figure 4 - Inheritance

tip
A default value can be set at the form-level for the type field that will automatically be saved in the database!

Index

An index is used to retrieve data from a database very fast. It can be used for a quick lookup for finding records searched frequently. An index can be made up of one field or multiple fields. The entire record then does not need to be used in the search to retrieve the row. Uniqueness can be applied to the field.