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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.osmos.io/datasets/getting-started/creating-primary-and-foreign-keys.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.