# Foreign Keys

## Overview

Foreign Key metadata configures relationships between array tables and parent tables. Foreign keys link array elements to parent records, enabling joins and maintaining referential integrity.

***

## Foreign Key Configuration

**Foreign Key Attribute Name (`FOREIGN_KEY_ATTRIBUTE_NAME`)**

* Parent attribute name to use as foreign key
* **Editable** - Select from available parent attributes
* **Required** - Must be valid attribute from parent table/array
* Can be from root table, parent array, or previously configured foreign keys
* Attributes must be active and included in code generation

***

**Foreign Key Alias Name (`FOREIGN_KEY_ALIAS_NAME`)**

* Custom column name alias for foreign key column in array table
* **Editable** - Optional; overrides default naming
* Default: Based on parent attribute name and polymorphic version (e.g., `customer_id_str`, `order_id_int`)
* Custom: Set for readability (e.g., `customer_id` instead of `customer_id_str`)

***

**Include In Security Row Level Policy (`INCLUDE_IN_SECURITY_ROW_LEVEL_POLICY`)**

* Boolean for including foreign key in row-level security policy
* **Editable** - `TRUE` = included in row access policy
* Requires Security Policy Integration product tier

**Security Row Level Policy Order (`SECURITY_ROW_LEVEL_POLICY_ORDER`)**

* Order of foreign key in security policy
* **Read-only** - Set by system; lower numbers evaluated first

***

## Foreign Key Relationships

**How foreign keys work:**

* Parent table contains referenced attribute
* Array table contains foreign key column
* Foreign key links array elements to parent records
* Enables joins: `orders.customer_id = customers.customer_id`

**Composite foreign keys:**

* Arrays can have multiple foreign keys
* Each links to different parent attribute
* All foreign keys included in array table
* Example: `order_items` with `order_id` and `product_id`

**Nested array foreign keys:**

* Nested arrays can reference parent array foreign keys
* Foreign keys from parent arrays available for selection
* Enables multi-level relationships
* Example: `order_items` can use `customer_id` from root and `order_id` from parent array

***

## Foreign Key Configuration Process

1. **Select array** - Navigate to Foreign Keys section, select array attribute from dropdown
2. **Add foreign key** - Select `FOREIGN_KEY_ATTRIBUTE_NAME` from dropdown, optionally set `FOREIGN_KEY_ALIAS_NAME` and `INCLUDE_IN_SECURITY_ROW_LEVEL_POLICY`
3. **Edit foreign key** - Modify fields as needed
4. **Delete foreign key** - Select row, check checkbox, press Delete key

***

## Foreign Key Generation

When foreign keys are configured:

1. **Foreign key columns** - Added to array table, named using alias (if provided) or default, data type matches parent attribute
2. **Foreign key values** - Populated from parent record, links array element to parent
3. **Join relationships** - Based on array relationship configuration (`RELATIONSHIP_TYPE`, `JOIN_TYPE`), supports left outer and inner joins

***

## Best Practices

**Foreign key selection:**

* Select attributes that uniquely identify parent records
* Prefer primary key attributes (`IS_PRIMARY_KEY = TRUE`)
* Use stable identifiers (not changing values)
* Multiple foreign keys: Use when array relates to multiple parents

**Naming:**

* Use clear, descriptive names for `FOREIGN_KEY_ALIAS_NAME`
* Avoid technical suffixes when possible (e.g., `customer_id` instead of `customer_id_str`)

**Security:**

* Set `INCLUDE_IN_SECURITY_ROW_LEVEL_POLICY = TRUE` for foreign keys with sensitive data
* Use for filtering array data by access rights (multi-tenant scenarios)

***

## Common Scenarios

**Simple array foreign key:**

1. Select `orders` array
2. Add foreign key: Select `customer_id` as `FOREIGN_KEY_ATTRIBUTE_NAME`
3. Set `FOREIGN_KEY_ALIAS_NAME = 'customer_id'`
4. Result: `orders` table includes `customer_id` column; enables joins

**Nested array foreign keys:**

1. Configure `orders` array foreign key: `customer_id`
2. Configure `order_items` array:
   * Foreign Key 1: `customer_id` (from root)
   * Foreign Key 2: `order_id` (from parent array)
3. Result: `order_items` table includes both foreign keys; enables multi-level joins

**Composite foreign keys:**

1. Add Foreign Key 1: `order_id` (from parent array)
2. Add Foreign Key 2: `product_id` (from root or another source)
3. Set appropriate alias names
4. Result: Both foreign keys included; enables complex joins

**Foreign key security:**

1. Set `INCLUDE_IN_SECURITY_ROW_LEVEL_POLICY = TRUE`
2. Create row access policy in Snowflake
3. Policy uses foreign key for filtering array data by access rights


---

# 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.datapancake.com/core-concepts/attribute-metadata/attribute-metadata-details/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.