Creating Primary and Foreign keys

In this section you will learn how to learn more about the different types of keys in Osmos Datasets.

Overview

A key field is a field used to uniquely identify records in a database table. They are used to ensure that no two records within a single table have the same value and by their nature must be unique. Osmos Datasets allows users to leverage Primary Keys and Foreign Keys to maintain referential integrity between tables.

Primary Keys

A primary key is a field in a table that uniquely identifies each record in the table. Only one Primary Key field may exist in a table. Primary keys cannot be NULL and they must be unique.

Best practice dictates that this key field header be named simply. Conventional names may look like TableName_ID or simply ID.

How to Assign a Primary Key In Datasets

Every table requires a unique Primary Key to be identified.

Note: If you plan to leverage a field in another table as a Foreign Key, it must be marked as a Primary Key in its original table.

Foreign Keys

A foreign key is a type of field that links two tables together. It is used to create relationships between different tables in a database.

For example, if you have a table of Item Master Records and a table of Quantity on Hand, you could use a foreign key to link Item Number to their Quantity on Hand table. The foreign key in the Quantity on Hand table would reference the Item Number ID primary key in the Quantity on Hand table, allowing you to easily query both tables and get the relevant information.

Note: A foreign key column is required to manage your list of valid options when creating a Dataset Table for an AI Value Mapping QuickFix.

How to Assign a Foreign Key In a Dataset Table

You can add one or more foreign keys when creating a Table. Note: A Foreign Key is optional.

Step 1: Create the Table.

Step 2: Upload or enter the Schema.

Step 3: Select Add Foreign Key.

1. Enter the Local Field Name: Enter the Display Name of your new Foreign Key

2. Select the Project: The location of the dataset where the project field resides

3. Select the Dataset: The name of the dataset where the referenced field resides

4. Select the Table: The name of the dataset where the referenced table resides

5. Select the Referenced Field: The name of the field that you wish to link

6. To Save, Select Add Foreign Key.

Note: The Referenced Field Foreign Key must be marked as a Primary Key on the source table.