# 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