Linking Databases Together

Insites uses 'Data Source' fields to implement foreign key constraints, which define relationships between database tables and allow users to build databases with the following features:

• Referential Integrity:

  The foreign key ensures that the data in one table (referred to as the child table) corresponds to the data in another table (referred to as the parent table). They define a relationship between the two tables based on a shared column or columns. The foreign key in the child table references the primary key in the parent table, establishing a link between the two.

• Data Consistency:

  The foreign key helps maintain data consistency by preventing orphaned or invalid data. With referential integrity enforced by the foreign key, you cannot insert a value into the child table's foreign key column that does not exist as a primary key in the parent table. This constraint ensures that data in the child table remains linked to valid and existing data in the parent table.

• Joins and Data Retrieval:

  The relationship established by the foreign key enables efficient data retrieval through joins. Joins combine data from multiple related tables based on the foreign key and primary key relationship. By joining tables, you can retrieve related data and perform complex queries involving data from multiple tables.

• Cascading Actions:

  Foreign key constraints can also define cascading actions. These actions automatically propagate changes across related tables. For example, you can specify actions such as cascading deletes or updates, where modifying or deleting a row in the parent table automatically affects the corresponding rows in the child table.

Creating a Data Source field

Before we proceed with creating a 'Data Source' field, note that there are two 'Data Source' field types:

• Data Source: Saves the value as a String and only saves one value per field.
• Data Source (Multiple): Saves values as an Array and saves multiple values per field. Used to set relations.

To link databases using foreign key constraints (referred to as 'Data Source' fields in this system):

1. Select Data from the main menu.
2. Select Databases.
3. Locate the database where you want to add a foreign key constraint ('Data Source' field) and select its Configuration row action link.
4. Navigate to the 'Fields' tab.
5. Select ADD FIELD.

6. Fill out the information required on the form. You may review the input requirements by visiting the following links:

   • Data Source
   • Data Source (Multiple)
7. Select SAVE FIELD to save your foreign key constraint.
8. Select SAVE to finalize your changes.

Conceptual example

Let's use two tables called and as an example. The table of orders () has a user column with a foreign key (data source) attribute that links it to the column in . Each row in the table of orders can then be associated with a specific user from the table of customers.

No orders can enter the system without a valid user being associated with them. The referential integrity and data consistency afforded by linking databases enable multiple users to place orders, check their status, and retrieve their order histories simultaneously. While this happens, other users can also update or cancel their orders as desired.

table_customer

table_orders

In this example, is the parent table while is the child table. 'CustomerID' in is the data source field used to link the two tables together. It references the 'CustomerID' field in .