---
title: "Foreign Keys"
---

# Foreign Keys

HeliosDB Nano supports FOREIGN KEY constraints for referential integrity between tables.

## Overview

Foreign keys provide:

- **Referential Integrity**: Ensure relationships between tables are valid
- **Cascading Actions**: Automatic updates/deletes of related rows
- **Data Consistency**: Prevent orphaned records
- **Flexible Enforcement**: IMMEDIATE, DEFERRED, or LOCK-FREE modes

## Syntax

### Basic Foreign Key

```sql
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    total DECIMAL
);
```

### Named Foreign Key Constraint

```sql
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);
```

### Composite Foreign Key

```sql
CREATE TABLE line_items (
    order_id INT,
    line_number INT,
    warehouse_id INT,
    location_id INT,
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (warehouse_id, location_id)
        REFERENCES locations(warehouse_id, location_id)
);
```

## Referential Actions

### ON DELETE Actions

| Action | Description |
|--------|-------------|
| `NO ACTION` | Reject delete if references exist (default) |
| `RESTRICT` | Same as NO ACTION, checked immediately |
| `CASCADE` | Delete referencing rows |
| `SET NULL` | Set foreign key columns to NULL |
| `SET DEFAULT` | Set foreign key columns to default value |

### ON UPDATE Actions

| Action | Description |
|--------|-------------|
| `NO ACTION` | Reject update if references exist (default) |
| `RESTRICT` | Same as NO ACTION, checked immediately |
| `CASCADE` | Update referencing rows |
| `SET NULL` | Set foreign key columns to NULL |
| `SET DEFAULT` | Set foreign key columns to default value |

### Examples

```sql
-- Cascade deletes
CREATE TABLE comments (
    id INT PRIMARY KEY,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    content TEXT
);

-- Set NULL on delete
CREATE TABLE employees (
    id INT PRIMARY KEY,
    manager_id INT REFERENCES employees(id) ON DELETE SET NULL,
    name TEXT
);

-- Cascade updates
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT REFERENCES orders(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    product_id INT REFERENCES products(id) ON DELETE RESTRICT
);
```

## Enforcement Modes

HeliosDB Nano supports three constraint enforcement modes:

### IMMEDIATE (Default)

Constraint checked immediately on each statement:

```sql
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id)
    -- Constraint checked on every INSERT/UPDATE
);
```

### DEFERRED

Constraint checked at transaction COMMIT:

```sql
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id)
        DEFERRABLE INITIALLY DEFERRED
);

-- Allows circular references within a transaction
BEGIN;
INSERT INTO orders VALUES (1, 100);  -- customer 100 doesn't exist yet
INSERT INTO customers VALUES (100, 'Alice');  -- now it does
COMMIT;  -- constraint checked here
```

### LOCK-FREE

Async validation for bulk operations (eventual consistency):

```sql
-- Set enforcement mode via SQL setting
SET constraint_enforcement = 'lock_free';

-- Bulk insert without blocking on FK validation
INSERT INTO orders SELECT * FROM staging_orders;

-- Constraints validated asynchronously
```

## Viewing Foreign Keys

### System Views

```sql
-- List all foreign key constraints
SELECT * FROM pg_foreign_keys;

-- View constraints for a specific table
SELECT
    constraint_name,
    table_name,
    column_name,
    references_table,
    references_column,
    on_delete,
    on_update
FROM pg_foreign_keys
WHERE table_name = 'orders';
```

### REPL Commands

```
\d orders           -- Shows table with FK constraints
\dt+ orders         -- Detailed table info including FKs
```

## Managing Foreign Keys

### Add Foreign Key (ALTER TABLE)

```sql
-- Add FK to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
```

### Drop Foreign Key

```sql
-- Drop FK constraint
ALTER TABLE orders DROP CONSTRAINT fk_customer;
```

## Integration with Other Features

### Branching

Foreign keys work with database branching:

```sql
-- Create branch
CREATE BRANCH dev FROM main AS OF NOW;

-- FK constraints are preserved in the branch
INSERT INTO orders VALUES (1, 999);  -- Fails if customer 999 doesn't exist
```

### Time-Travel

Historical queries respect FK relationships:

```sql
-- Query historical data with FK joins
SELECT o.*, c.name
FROM orders AS OF TIMESTAMP '2025-01-01' o
JOIN customers AS OF TIMESTAMP '2025-01-01' c ON o.customer_id = c.id;
```

### Multi-Tenancy

Foreign keys work with tenant isolation:

```sql
-- FK within tenant scope
CREATE TABLE tenant_orders (
    id INT,
    tenant_id INT,
    customer_id INT,
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, customer_id)
        REFERENCES tenant_customers(tenant_id, id)
);
```

## Best Practices

1. **Always define FKs**: Use foreign keys to enforce data relationships
2. **Choose appropriate actions**: Use CASCADE carefully, RESTRICT for safety
3. **Index FK columns**: Improves JOIN performance and FK validation
4. **Consider deferrable**: Use for complex transactions with circular references
5. **Use naming conventions**: `fk_<table>_<column>__<ref_table>`

## Limitations

- Self-referencing FKs supported (e.g., employee → manager)
- Maximum 16 columns per composite FK
- Referenced columns must have PRIMARY KEY or UNIQUE constraint

## See Also

- [Primary Keys](https://www.heliosdb.com/docs/nano/features/primary-keys/)
- [SQL Reference](https://www.heliosdb.com/docs/nano/user-guide/sql-reference/)
- [Multi-Tenancy](https://www.heliosdb.com/docs/nano/features/multi-tenancy/)