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 in order to maintain referential integrity between tables.
Primary Keys
A primary key is a field in a table which 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 it's 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.
How to Assign a Foreign Key In Datasets
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.
Enter the Local Field Name: Enter the Display Name of your new Foreign Key
Select the Project: The location of the dataset where the project field resides
Select the Dataset: The name of the dataset where the referenced field resides
Select the Table: The name of the dataset where the referenced table resides
Select the Referenced Field: The name of the field that you wish to link
To Save, Select Add Foreign Key.
Note: The Referenced Field Foreign Key must be marked as a Primary Key on the source table.
Last updated
