# Creating Primary and Foreign keys

## 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.

<figure><img src="https://353417064-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MYrsDW6vGBTygB1qqSE%2Fuploads%2FygMVaPqcMK1ebtOClhZ1%2FCleanShot%202023-10-17%20at%2011.19.09%402x.png?alt=media&#x26;token=ea166162-65c5-4500-a883-ae60bbd2a072" alt=""><figcaption></figcaption></figure>

### 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.&#x20;

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.

{% hint style="info" %}
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](https://docs.osmos.io/data-transformations/ai-value-mapping).
{% endhint %}

#### 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.&#x20;

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**.&#x20;

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

<figure><img src="https://353417064-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MYrsDW6vGBTygB1qqSE%2Fuploads%2F32LM0ZkQgayJS6FOgZ8L%2FCleanShot%202023-04-12%20at%2022.40.47%402x.png?alt=media&#x26;token=8e80b8cf-b795-4a10-a018-e20a993e95db" alt=""><figcaption></figcaption></figure>