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

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

Named Foreign Key Constraint

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

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

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

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:

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):

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

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

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

Drop Foreign Key

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

Integration with Other Features

Branching

Foreign keys work seamlessly with database branching:

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

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

-- 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
• SQL Reference
• Multi-Tenancy