Index Types
Last updated 19/03/2024
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.
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.
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.
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.
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!