Foreign Keys

Configuration for foreign key relationships between array tables and parent tables, including parent attribute selection, column aliases, and row-level security policy integration.

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